Export SAS datasets to delimited files. Charles Burchill Manitoba Centre for Health Policy and Evaluation Create a delimited file from any SAS dataset . The ouput file can be downloaded to a PC and opened by Quattro, or Lotus, or any other program that reads delimited files. This macro is most useful when using batch SAS. Under SAS 6.12 delimited files can be exported directly from the program editor using the Export menu item under File or the EXPORT command. Macro Call _LOTUS ; Options input - Input data set name input=SAS_dataset_name output - This is the name of the file with the delimited data. It should be defined as a filename earlier in the program or a file name inclosed in quotes. Note: Many programs will not read delimited files with a line length greater than 240 characters. Remember to account for the length of the delimiter. output="/home/cpe/burchil/output.txt" comment - Put this line as the first line in the output file. This string must be in quotes. comment='This is an output file' Caution: Do not use the '=' symbol as part of the comment string. var - List of variables to save in dataset. This string must be in quotes (e.g. var='region age sex'. The variables will be saved in the order they are listed. Default is to save all variables into the file. type - Type of output. comma, qc, or tab. comma and qc will output a quote comma seperated file tab will output a tab seperated file. qc is the default. This type of output will maintain compatability with the previous version (no formated numerics) and can be correctly imported into quattro pro. The comma type will use the formated values of numeric variables. Note that character strings will not be quoted and may not import correctly. Note: The delim option takes precedence over the type option. label - Use available variable names or labels for column titles. Options - label, name. Default name. Labels will be truncated at 40 characters. label=name lrecl - logical record length. If you want your output to be longer Than 256 columns you must specify the logical record length. lrecl=356 Excel will read files wider than 256 characters. delim - If you want to specify your own delimiter you can. This takes precedence over the type option. It must be a quoted string, or character. delim='$' delim='09'x (OK I know this is also set with type=tab). addfile - Add the contents of the system variable SAS_SYSIN_INFO to the top of the output file. This option only works with batch SAS. addfile=yes Note: Just putting 'addfile' into the macro call is enough. debug - turn on mprint and notes. debug=debug Note: Just putting 'debug' into the macro call is enough. Output A delimited file with the name defined in the ouput statement. Example calls _lotus input=smdata output="/home/cpe/burchil/output/hosp.out" * Create quote comma seperated file with the name hosp.out ; filename hosp '/home/cpe/burchil/output/hosp.out' ; _lotus output=hosp ; * Create a quote comma seperated file with the name hosp.out, using the last active SAS dataset, and a filename statement. ; Additional Information. Using the comma, or tab option the formated values of all variables are exported. Character formats from numeric variables will not be quoted. Quotes should be added to the format. In general I would not recommend using character formated numeric variables, put the formated variable into a new variable. Using the qc option this macro will not properly export formated numeric variables. Only the underlying numbers will be exported. The suggested work around for this problem is to put any character labels into a new character variable, or input formated numeric variables into a new variable. Missing numeric variables are exported as empty character cells. Quattro and Lotus will not import missing numeric cells from a quote comma delimited file. To export missing numeric variables as empty cells the variables must be re-formated. This means any existing formats are not carried over to the output file: the original dataset, temporary or permanent, is not affected. This program re-sets the missing system option to '.'. The total line length that lotus will import is 240 characters, Quattro pro will import up to 256 characters. Make sure that any line in the ouput file does not exceed this number (remember to account for added commas, and quotes). Excel does not appear to have this limitation so I may add a line size option if necessary at a later date - request it if you need it. You should avoid using variable names (or labels) or variable formats that the importing program will interpret as a function of sime kind (e.g. Excel will convert 1-3 to 1-Jan-1995). Note: The following messages are not errors: The type=qc option generates a format called _MISSIT on the fly to convert missings to " ". WARNING: Format _MISSIT is already on the library. If there are no numeric, or character variables then the following warning is issued. This warning may also be generated when the specified variables do not exist on the data set. WARNING: Defining an array with zero elements.