dsread

Version 1.3, 2010-04-07

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.

This version 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 and other software

New in version 1.3: dsread can now read datasets that have been compressed using CHAR compression (the most common type).

Download the zip file and save dsread.exe anywhere in your path - C:\WINDOWS will do. To uninstall, just delete the file - dsread doesn't leave any other files or registry entries on your machine.

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 name 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

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...

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 1.3 (2010-04-07) Usage: dsread [/?] [/h] [/c] [/v] [/l] [/L] [/t] ... /?: 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. /l: Lossless data output - writes numerics as if using HEX16. format. /L: As above but bytes will be output in big-endian order. /t: Tab-delimited - data output will be as CSV but tab-delimited. /p: Output performance/progress messages to standard error. You can use - instead of / when specifying options. With no options specified, list summary details of datasets. http://www.oview.co.uk/dsread, 2010.

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.

More info will appear here in time. Programmers may also be interested in SaviDataSet which is a DLL for both reading and writing SAS7BDAT files.

Limitations

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

It will only work on Windows-format SAS7BDAT files for now, 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.

Formats: dsread reads the names of formats applied to variables in SAS7BDAT files and lists them in the contents list. When outputting data, most formats are ignored and only the underlying numeric and character values are output. The exceptions are DATE, 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 now 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 still be rejected by dsread.

Change History

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.