Appendix 3: SAS Utility Macro

%DS2CSV Macro

converts SAS data sets to comma-separated value (CSV) files

Valid: in a DATA step

Syntax

%DS2CRV( argument=value , argument=value ,...)

Arguments That Affect Input/Output

csvfile= external-filename

• specifies the name of the CSV file where the formatted output is to be written. If the file that you specify does not exist, then it is created for you.

  Note: Do not use the CSVFILE argument if you use the CSVFREF argument.

csvfref= fileref

• specifies the SAS fileref that points to the location of the CSV file where the formatted output is to be written. If the file that you specify does not exist, then it is created for you.

  Note: Do not use the CSVFREF argument if you use the CSVFILE argument.

openmode =REPLACEAPPEND

• indicates whether the new CSV output overwrites the information that is currently in the specified file or if the new output is appended to the end of the existing file. The default value is REPLACE. If you do not want to replace the current contents, then specify OPENMODE=APPEND to add your new CSV-formatted output to the end of an existing file.

  Note: OPENMODE=APPEND is not valid if you are writing your resulting output to a partitioned data set (PDS) on z/OS.

Arguments That Affect MIME/HTTP Headers

For more information about MIME and HTTP headers, refer to the Internet Request for Comments (RFC) documents RFC 1521 (http://asg.web.cmu.edu/rfc/rfc1521.html) and RFC 1945 (http://asg.web.cmu.edu/rfc/rfc1945.html), respectively.

conttype=Y N

• indicates whether to write a content type header. This header is written by default.

  Restriction: This argument is valid only when RUNMODE=S.

contdisp=Y N

• indicates whether to write a content disposition header. This header is written by default.

  Note: If you specify CONTDISP=N, then the SAVEFILE argument is ignored.

  Restriction: This argument is valid only when RUNMODE=S.

mimehdr1= MIME/HTTP-header

• specifies the text that is to be used for the first MIME or HTTP header that is written. This header is written after the content type and disposition headers. By default, nothing is written for this header.

  Restriction: This argument is valid only when RUNMODE=S.

mimehdr2= MIME/HTTP-header

• specifies the text that is to be used for the second MIME or HTTP header that is written. This header is written after the content type and disposition headers. By default, nothing is written for this header.

  Restriction: This argument is valid only when RUNMODE=S.

mimehdr3= MIME/HTTP-header

• specifies the text that is to be used for the third MIME or HTTP header that is written when RUNMODE=S is specified. This header is written after the content type and disposition headers. By default, nothing is written for this header.

  Restriction: This argument is valid only when RUNMODE=S.

mimehdr4= MIME/HTTP-header

• specifies the text that is to be used for the fourth MIME or HTTP header that is written. This header is written after the content type and disposition headers. By default, nothing is written for this header.

  Restriction: This argument is valid only when RUNMODE=S.

mimehdr5= MIME/HTTP-header

• specifies the text that is to be used for the fifth MIME or HTTP header that is written. This header is written after the content type and disposition headers. By default, nothing is written for this header.

runmode=S B

• specifies whether you are running the %DS2CSV macro in batch or server mode. The default setting for this argument is RUNMODE=S.

  • Server mode (RUNMODE=S) is used with Application Dispatcher programs and streaming output stored processes. Server mode causes DS2CSV to generate appropriate MIME or HTTP headers. For more information about Application Dispatcher, refer to the Application Dispatcher documentation at http://support.sas.com/rnd/web/intrnet/dispatch.html.

  • Batch mode (RUNMODE=B) means that you are submitting the DS2CSV macro in the SAS Program Editor or that you included it in a SAS program.

• Note: No HTTP headers are written when you specify batch mode.

• Restriction: RUNMODE=S is valid only when used within the SAS/IntrNet and Stored Process servers.

savefile= filename

• specifies the filename to display in the Web browser s Save As dialog box. The default value is the name of the data set plus .csv .

  Note: This argument is ignored if CONTDISP=N is specified.

  Restriction: This argument is valid only when RUNMODE=S.

Arguments That Affect CSV Creation

colhead=Y N

• indicates whether to include column headings in the CSV file. The column headings that are used depend on the setting of the LABELS argument. By default, column headings are included as the first record of the CSV file.

data= SAS-data-set-name

• specifies the SAS data set that contains the data that you want to convert into a CSV file. This argument is required. However, if you omit the data set name, DS2CSV attempts to use the most recently created SAS data set.

formats=Y N

• indicates whether to apply the data set s defined variable formats to the values in the CSV file. By default, all formats are applied to values before they are added to the CSV file. The formats must be stored in the data set in order for them to be applied.

labels=Y N

• indicates whether to apply the data set s defined variable formats to the values in the CSV file. By default, all formats are applied to values before they are added to the CSV file. The formats must be stored in the data set in order for them to be applied.

pw= password

• specifies the password that is needed to access a password-protected data set. This argument is required if the data set has a READ or PW password. (You do not need to specify this argument if the data set has only WRITE or ALTER passwords.)

sepchar= separator-character

• specifies the character that is used for the separator character. Specify the two-character hexadecimal code for the character or omit this argument to get the default setting. The default settings are 2C for ASCII systems and 6B for EBCDIC systems. (These settings represent commas (,) on their respective systems.)

var= var1 var2 ...

• specifies the variables that are to be included in the CSV file and the order in which they should be included. To include all of the variables in the data set, do not specify this argument. If you want to include only a subset of the variables, then list each variable name and use single blank spaces to separate the variables . Do not use a comma in the list of variable names .

where= where-expression

• specifies the variables that are to be included in the CSV file and the order in which they should be included. To include all of the variables in the data set, do not specify this argument. If you want to include only a subset of the variables, then list each variable name and use single blank spaces to separate the variables. Do not use a comma in the list of variable names.

Details

The DS2CSV macro converts SAS data sets to comma-separated value (CSV) files. You can specify the hex code for the separator character if you want to create some other type of output file (for example, a tab-separated value file).

Example

The following example uses the %DS2CSV macro to convert the SASHELP.RETAIL data set to a comma-separated value file:

 data sashelp.retail;     %ds2csv (data=sashelp.retail, runmode=b, csvfile=c:\temp\retail.csv);  run;