dsread

Version 2.2, 2013-02-23

dsread is a simple command-line utility for working with datasets in the SAS7BDAT file format, as produced by the SAS System, the World Programming System, StatTransfer and others.

Version 2.2 fixes a bug which caused a crash when trying to read a dataset that uses BINARY compression, and also adds a new supported platform so if you've tried dsread in the past and found it couldn't read your datasets, please try again. (In general, if you find a dataset that dsread can't open, please let me know).

Versions 2.x are a major upgrade over earlier versions, and can read SAS7BDAT files from a much wider variety of platforms, including 64-bit Windows, Linux, Solaris and others. Reading datasets from these new platforms is restricted in the trial version of dsread - please register to get full functionality (just US$75 - see below for details).

dsread lets you:

• list the summary details of datasets
• list the contents (the variable names and other attributes*) of datasets
• convert a dataset to CSV format for import into Excel, R, KNIME and other software
• convert a dataset to SQL source format for import into mySql and other database systems

See Also - DsShell

dsread is very useful when you need a command-line tool; for a more user-friendly experience, please try our latest product - DsShell, a Windows shell extension for SAS7BDAT files.

Download

Download the zip file and save dsread.exe anywhere in your PATH - C:\WINDOWS will do. You can store dsread.exe in a new folder but you'll then need to add that folder to your PATH - see here for further information on how to do that. To uninstall, just delete the file - dsread doesn't leave any other files or registry entries on your machine.

Registration

This version of dsread is a free trial which you can download to make sure that it does everything that you want. If you decide to carry on using dsread, you should buy a registration code via RegNow by clicking here. Registration costs US$75 for a lifetime license which will work with all future versions of dsread at no extra cost; please enquire for multi-user and academic discounts.

Some functions of dsread are restricted until you get your registration code - the SQL output will only convert the first 50 observations of a dataset, and the lossless output and performance/progress monitoring are disabled completely. Conversion to CSV is fully functional in the trial version, but only for datasets created in 32-bit Windows format; conversion of datasets from other platforms is restricted to the first 50 observations only.

Your dsread registration code will also work with DsShell at no extra cost.

Usage

To use dsread, start a Command Prompt and enter:

dsread [flags] [filename]

For example, to get a summary of all datasets in the current directory whose names start with 'p':

sashelp> dsread p* Name Vars Obs Label ----------------------------------------------------------------- PRDSAL2 11 23040 PRDSAL3 11 11520 PRDSALE 10 1440

To see the content (ie variable structure) of one of the datasets:

sashelp> dsread /c prdsale Contents of dataset PRDSALE Name Type Len Format Label ----------------------------------------------------------------- ACTUAL Num 8 DOLLAR12.2 Actual Sales PREDICT Num 8 DOLLAR12.2 Predicted Sales COUNTRY Char 10 $CHAR10. Country REGION Char 10 $CHAR10. Region DIVISION Char 10 $CHAR10. Division PRODTYPE Char 10 $CHAR10. Product type PRODUCT Char 10 $CHAR10. Product QUARTER Num 8 8. Quarter YEAR Num 8 4. Year MONTH Num 8 MONNAME3. Month

To convert the dataset to CSV:

sashelp> dsread /v prdsale ACTUAL,PREDICT,COUNTRY,REGION,DIVISION,PRODTYPE,PRODUCT,QUARTER,YEAR,MONTH 925,850,CANADA,EAST,EDUCATION,FURNITURE,SOFA,1,1993,12054 999,297,CANADA,EAST,EDUCATION,FURNITURE,SOFA,1,1993,12085 608,846,CANADA,EAST,EDUCATION,FURNITURE,SOFA,1,1993,12113 642,533,CANADA,EAST,EDUCATION,FURNITURE,SOFA,2,1993,12144 656,646,CANADA,EAST,EDUCATION,FURNITURE,SOFA,2,1993,12174 948,486,CANADA,EAST,EDUCATION,FURNITURE,SOFA,2,1993,12205 612,717,CANADA,EAST,EDUCATION,FURNITURE,SOFA,3,1993,12235 114,564,CANADA,EAST,EDUCATION,FURNITURE,SOFA,3,1993,12266 ...etc...

You'll probably want to redirect the output of a 'dsread /v' into another file, like this:

dsread /v prdsale > prdsale.csv

or you can specify the output file using the /o option, like this (note there is no space after the '/o'):

dsread /v /oprdsale.csv prdsale

You can combine the /c and /v flags to get the contents listing in CSV format:

sashelp> dsread /c /v prdsale DATASET,VARNUM,NAME,TYPE,LENGTH,FORMAT,LABEL PRDSALE,1,ACTUAL,Num,8,DOLLAR12.2,Actual Sales PRDSALE,2,PREDICT,Num,8,DOLLAR12.2,Predicted Sales PRDSALE,3,COUNTRY,Char,10,$CHAR10.,Country PRDSALE,4,REGION,Char,10,$CHAR10.,Region PRDSALE,5,DIVISION,Char,10,$CHAR10.,Division PRDSALE,6,PRODTYPE,Char,10,$CHAR10.,Product type PRDSALE,7,PRODUCT,Char,10,$CHAR10.,Product PRDSALE,8,QUARTER,Num,8,8.,Quarter PRDSALE,9,YEAR,Num,8,4.,Year PRDSALE,10,MONTH,Num,8,MONNAME3.,Month

Use /t to get tab-delimited output:

sashelp> dsread /v /t prdsale ACTUAL PREDICT COUNTRY REGION DIVISION PRODTYPE PRODUCT QUARTER YEAR MONTH 925 850 CANADA EAST EDUCATION FURNITURE SOFA 1 1993 12054 999 297 CANADA EAST EDUCATION FURNITURE SOFA 1 1993 12085 608 846 CANADA EAST EDUCATION FURNITURE SOFA 1 1993 12113 642 533 CANADA EAST EDUCATION FURNITURE SOFA 2 1993 12144 656 646 CANADA EAST EDUCATION FURNITURE SOFA 2 1993 12174 948 486 CANADA EAST EDUCATION FURNITURE SOFA 2 1993 12205 612 717 CANADA EAST EDUCATION FURNITURE SOFA 3 1993 12235 114 564 CANADA EAST EDUCATION FURNITURE SOFA 3 1993 12266 ...etc...

Use /q to get SQL source output:

sashelp> dsread /q prdsale CREATE TABLE `prdsale` ( `ACTUAL` DOUBLE, `PREDICT` DOUBLE, `COUNTRY` VARCHAR(10), `REGION` VARCHAR(10), `DIVISION` VARCHAR(10), `PRODTYPE` VARCHAR(10), `PRODUCT` VARCHAR(10), `QUARTER` DOUBLE, `YEAR` DOUBLE, `MONTH` DOUBLE ) COMMENT=''; INSERT INTO `prdsale` VALUES ('925', '850', 'CANADA', 'EAST', 'EDUCATION', 'FURNITURE', 'SOFA', '1', '1993', '12054'); INSERT INTO `prdsale` VALUES ('999', '297', 'CANADA', 'EAST', 'EDUCATION', 'FURNITURE', 'SOFA', '1', '1993', '12085'); INSERT INTO `prdsale` VALUES ('608', '846', 'CANADA', 'EAST', 'EDUCATION', 'FURNITURE', 'SOFA', '1', '1993', '12113'); INSERT INTO `prdsale` VALUES ('642', '533', 'CANADA', 'EAST', 'EDUCATION', 'FURNITURE', 'SOFA', '2', '1993', '12144'); INSERT INTO `prdsale` VALUES ('656', '646', 'CANADA', 'EAST', 'EDUCATION', 'FURNITURE', 'SOFA', '2', '1993', '12174'); INSERT INTO `prdsale` VALUES ('948', '486', 'CANADA', 'EAST', 'EDUCATION', 'FURNITURE', 'SOFA', '2', '1993', '12205'); INSERT INTO `prdsale` VALUES ('612', '717', 'CANADA', 'EAST', 'EDUCATION', 'FURNITURE', 'SOFA', '3', '1993', '12235'); ...etc...

Converting the IEEE floating-point numeric values in the SAS7BDAT file to decimal frequently causes a loss of precision. To get a lossless representation of the data, use the /l flag:

sashelp> dsread /v /l prdsale ACTUAL,PREDICT,COUNTRY,... 0x0000000000e88c40,0x0000000000908a40,CANADA... 0x0000000000388f40,0x0000000000907240,CANADA... 0x0000000000008340,0x0000000000708a40,CANADA... 0x0000000000108440,0x0000000000a88040,CANADA... 0x0000000000808440,0x0000000000308440,CANADA... 0x0000000000a08d40,0x0000000000607e40,CANADA... ...etc...

The numerics are output as eight hexadecimal bytes (16 digits) giving the internal floating-point representation, which can then be used to reconstruct the exact same value in the receiving software. Use /L to get the bytes in big-endian order

Use the '/?' flag for help:

sashelp> dsread /? dsread: A reader for files in SAS7BDAT format. Version 2.2 (2013-02-23) Usage: dsread [[/option]...] ... /?: Show this usage message and exit. /h: Show this usage message and exit. /c: List the variables in the dataset and their attributes. /v: Write a CSV representation of the dataset to standard output. /t: Tab-delimited - data output will be as CSV but tab-delimited. /o: Send output to the named file (eg /omydata.csv). /u: Check for newer version (requires internet access). * /q: Write an SQL representation of the dataset to standard output. * /l: Lossless data output - writes numerics as if using HEX16. format. * /L: As above but bytes will be output in big-endian order. * /p: Output performance/progress messages to standard error. Options marked * will only work for registered users. You can use - instead of / when specifying options. With no options specified, list summary details of datasets. http://www.oview.co.uk/dsread, 2010-2013. Registration file (C:\Users\Chris\Documents\dsread_registration.txt) not found. Unregistered - options marked * are unavailable.

Use the '/u' flag to quickly check for updates online:

sashelp> dsread /u Contacting www.oview.co.uk for latest version details... You already have the latest version of dsread; no updates available.

The CSV output conforms to the RFC 4180 spec. Both Excel and Open Office's Calc read the output correctly and automatically. Note that CSV output from PROC EXPORT and the Universal Viewer doesn't conform to RFC 4180; values containing special characters like new-lines and double-quotes will not be quoted correctly.

Limitations

This software should be considered experimental and is not guaranteed to be accurate. You use it at your own risk.

It will work on most SAS7BDAT files, either uncompressed or using 'CHAR' compression. It should only be used on 'clean' datasets, by which I mean datasets that are the immediate output of a data step or PROC. Using dsread on other datasets, for example datasets that have been edited using FSEDIT or similar, might lead to strange effects like deleted observations being output.

Another strange effect is that dsread will happily work on datasets that have a READ password. The password protection in SAS7BDAT files does NOT encrypt the data and dsread doesn't even check to see whether a password has been set. You should not rely on a READ password to protect your data from prying eyes - use the ENCRYPT option for that. See SAS's rather cryptic warning about this here.

Before attempting to read a dataset, dsread checks its 'data requirements' information. If this isn't recognised, dsread will stop with an 'Unknown data requirements' error. If you have a SAS7BDAT file that triggers this error, please let me know () so that dsread can be tested against it.

Formats: dsread will correctly read the names of formats applied to variables in SAS7BDAT files and will show the format names in the contents list. However, when outputting data, most formats are ignored and only the underlying numeric and character values are output. The exceptions are DATE, DATETIME, YYMMDD and TIME, for which values will be converted appropriately, though note that the length specified for the format will be ignored - that is, any DATEn. format will be output in the same format, whether it's DATE9., DATE12. etc.

Compression: dsread can read datasets compressed with CHAR compression. This is a simple run-length-encoding compression and is the default type when you create a dataset with either the COMPRESS=YES or COMPRESS=CHAR options. The only other documented compression scheme is BINARY, which is only used when COMPRESS=BINARY is specified. Datasets with BINARY compression will be rejected by dsread.

Change History

Version 2.2:

• Fixed bug that caused a crash when opening a dataset using BINARY compression. The data from such datasets cannot be read (though the contents listing can still be produced) but the error will be reported gracefully.
• Added support for a new platform - datasets created in little-endian format on Linux/Intel machines (many thanks to the user who reported this new platform).
• Improved the messages related to the registration file - the expected location of the file will now be reported in the '/?' output to assist in diagnosing registration problems.

Version 2.1:

• Bug fix over 2.0; wide datasets now handled correctly.

Version 2.0:

• Datasets from most 32-bit and 64-bit operating systems now supported, including Windows, Linux and Solaris.

Version 1.8:

• Added the WIN and WIN_VSHO platform identifiers.

Version 1.7:

• Added the /o flag to send output directly to a named output file. This avoids the need to redirect standard output, which may be helpful in certain situations.

Version 1.6:

• Added several new supported Windows platforms

Version 1.5:

• Added WIN_ASRV as a supported platform
• Improved error message when an unsupported platform is encountered

Version 1.4:

• Improved memory usage for large datasets
• Added DATETIME format support
• Added SQL output format
• Added registration code handling
• Upgraded to Visual Studio 2010 compiler (dsread will no longer work on Windows 2000 or earlier)

Version 1.3:

• Added compression support
• Added performance/progress reporting (/p option)
• Improved performance on large datasets
• Improved formatting of numeric output

*: It's one of SAS's many quirks that the structure of a dataset is referred to as its 'contents', which can cause understandable confusion if you think of the actual data in the dataset to be its contents.