Create a CSV file without column names/headers in row 1

From sasCommunity

This is a regularly recurring question on SAS-L.

The following is a stub article with a number of alternatives listed created initially by Don Henderson with the hope/expectation that the SAS-L community will fill in the details and perhaps add even more ideas (and then, of course, delete this paragraph).

Note also that most of these techniques can be used to create a delimited file using any defined delimiter (e.g., tab delimited, pipe (|) delimited, etc.).

Each of the techniques is also slightly different in how, and if, the values are quoted.

1 Run PROC EXPORT and recall and edit the code
2 Run PROC EXPORT and use a DATA step to rewrite the file without the first row
3 DATA _NULL_ with a PUT statement
4 DATA _NULL_ with a PUT statement, all fields quoted
5 ODS CSV and PROC REPORT with suppressed column headers
6 The %ds2csv SAS Institute utility macro

[edit] Run PROC EXPORT and recall and edit the code

[edit] Run PROC EXPORT and use a DATA step to rewrite the file without the first row

Sample program

filename exTemp temp;
proc export data=sashelp.class outfile=exTemp dbms=csv;
   run;
data _null_;
   infile extemp firstobs=2;
   file '.\class.csv';
   input;
   put _infile_;
   run;

Sample log

25   filename exTemp temp;
26   proc export data=sashelp.class outfile=exTemp dbms=csv;
27      run;

28    /**********************************************************************
29    *   PRODUCT:   SAS
30    *   VERSION:   9.1
31    *   CREATOR:   External File Interface
32    *   DATE:      25AUG08
33    *   DESC:      Generated SAS Datastep Code
34    *   TEMPLATE SOURCE:  (None Specified.)
35    ***********************************************************************/
36       data _null_;
37       set  SASHELP.CLASS                                end=EFIEOD;
38       %let _EFIERR_ = 0; /* set the ERROR detection macro variable */
39       %let _EFIREC_ = 0;     /* clear export record count macro variable */
40       file EXTEMP delimiter=',' DSD DROPOVER ;
41          format Name $8. ;
42          format Sex $1. ;
43          format Age best12. ;
44          format Height best12. ;
45          format Weight best12. ;
46       if _n_ = 1 then        /* write column names */
47        do;
48          put
49          'Name'
50          ','
51          'Sex'
52          ','
53          'Age'
54          ','
55          'Height'
56          ','
57          'Weight'
58          ;
59        end;
60        do;
61          EFIOUT + 1;
62          put Name $ @;
63          put Sex $ @;
64          put Age @;
65          put Height @;
66          put Weight ;
67          ;
68        end;
69       if _ERROR_ then call symputx('_EFIERR_',1);  /* set ERROR detection macro variable */
70       if EFIEOD then call symputx('_EFIREC_',EFIOUT);
71       run;

NOTE: The file EXTEMP is:
      File Name=C:\DOCUME~1\SYSUSERID\LOCALS~1\Temp\SAS Temporary Files\_TD3548\#LN00038,
      RECFM=V,LRECL=256

NOTE: 20 records were written to the file EXTEMP.
      The minimum record length was 17.
      The maximum record length was 26.
NOTE: There were 19 observations read from the data set SASHELP.CLASS.
NOTE: DATA statement used (Total process time):
      real time           0.08 seconds
      cpu time            0.02 seconds


19 records created in EXTEMP from SASHELP.CLASS                            .


NOTE: "EXTEMP" was successfully created.
NOTE: PROCEDURE EXPORT used (Total process time):
      real time           2.31 seconds
      cpu time            0.21 seconds


72   data _null_;
73      infile extemp firstobs=2;
74      file '.\class.csv';
75      input;
76      put _infile_;
77      run;

NOTE: The infile EXTEMP is:
      File Name=C:\DOCUME~1\SYSUSERID\LOCALS~1\Temp\SAS Temporary Files\_TD3548\#LN00038,
      RECFM=V,LRECL=256

NOTE: The file '.\class.csv' is:
      File Name=C:\Documents and Settings\SYSUSERID\My Documents\My SAS Files\9.1\class.csv,
      RECFM=V,LRECL=256

NOTE: 19 records were read from the infile EXTEMP.
      The minimum record length was 17.
      The maximum record length was 21.
NOTE: 19 records were written to the file '.\class.csv'.
      The minimum record length was 17.
      The maximum record length was 21.
NOTE: DATA statement used (Total process time):
      real time           0.02 seconds
      cpu time            0.00 seconds

Sample output file

Alfred,M,14,69,112.5
Alice,F,13,56.5,84
Barbara,F,13,65.3,98
Carol,F,14,62.8,102.5
Henry,M,14,63.5,102.5
James,M,12,57.3,83
Jane,F,12,59.8,84.5
Janet,F,15,62.5,112.5
Jeffrey,M,13,62.5,84
John,M,12,59,99.5
Joyce,F,11,51.3,50.5
Judy,F,14,64.3,90
Louise,F,12,56.3,77
Mary,F,15,66.5,112
Philip,M,16,72,150
Robert,M,12,64.8,128
Ronald,M,15,67,133
Thomas,M,11,57.5,85
William,M,15,66.5,112

[edit] DATA _NULL_ with a PUT statement

Sample program, use of FILE statement LRECL= may be necessary.

Data _null_;   
   file '.\class.csv' dsd dlm=',';
   set sashelp.class ;
   put (_all_) (+0);
   run;

Sample Log.

1    Data _null_;
2       file '.\class.csv' dsd dlm=',';
3       set sashelp.class ;
4       put ( _all_ ) (+0);
5       run;

NOTE: The file '.\class.csv' is:
      File Name=C:\Documents and Settings\SYSUSERID\My Documents\My SAS Files\9.1\class.csv,
      RECFM=V,LRECL=256

NOTE: 19 records were written to the file '.\class.csv'.
      The minimum record length was 17.
      The maximum record length was 21.
NOTE: There were 19 observations read from the data set SASHELP.CLASS.
NOTE: DATA statement used (Total process time):
      real time           0.08 seconds
      cpu time            0.01 seconds

Sample output file.

Alfred,M,14,69,112.5
Alice,F,13,56.5,84
Barbara,F,13,65.3,98
Carol,F,14,62.8,102.5
Henry,M,14,63.5,102.5
James,M,12,57.3,83
Jane,F,12,59.8,84.5
Janet,F,15,62.5,112.5
Jeffrey,M,13,62.5,84
John,M,12,59,99.5
Joyce,F,11,51.3,50.5
Judy,F,14,64.3,90
Louise,F,12,56.3,77
Mary,F,15,66.5,112
Philip,M,16,72,150
Robert,M,12,64.8,128
Ronald,M,15,67,133
Thomas,M,11,57.5,85
William,M,15,66.5,112

[edit] DATA _NULL_ with a PUT statement, all fields quoted

This example uses the ~ format modifier to quote all the fields in the CSV file.

Sample program

Data _null_;   
   file '.\class.csv' dsd dlm=',';
   set sashelp.class ;
   put ( _all_ ) (~);
   run;

Sample log

80   Data _null_;
81      file '.\class.csv' dsd dlm=',';
82      set sashelp.class ;
83      put ( _all_ ) (~);
84      run;

NOTE: The file '.\class.csv' is:
      File Name=C:\Documents and Settings\SYSUSERID\My Documents\My SAS Files\9.1\class.csv,
      RECFM=V,LRECL=256

NOTE: 19 records were written to the file '.\class.csv'.
      The minimum record length was 27.
      The maximum record length was 31.
NOTE: There were 19 observations read from the data set SASHELP.CLASS.
NOTE: DATA statement used (Total process time):
      real time           0.02 seconds
      cpu time            0.01 seconds

Sample output file

"Alfred","M","14","69","112.5"
"Alice","F","13","56.5","84"
"Barbara","F","13","65.3","98"
"Carol","F","14","62.8","102.5"
"Henry","M","14","63.5","102.5"
"James","M","12","57.3","83"
"Jane","F","12","59.8","84.5"
"Janet","F","15","62.5","112.5"
"Jeffrey","M","13","62.5","84"
"John","M","12","59","99.5"
"Joyce","F","11","51.3","50.5"
"Judy","F","14","64.3","90"
"Louise","F","12","56.3","77"
"Mary","F","15","66.5","112"
"Philip","M","16","72","150"
"Robert","M","12","64.8","128"
"Ronald","M","15","67","133"
"Thomas","M","11","57.5","85"
"William","M","15","66.5","112"

[edit] ODS CSV and PROC REPORT with suppressed column headers

To create CSV files (i.e., this technique won't work for other types of delimited files), ODS CSV can be used with PROC REPORT. The ' ' in the DEFINE statement specifies that no column header text is to be included. Since none of the columns have header text, the header row is suppressed.

Sample program

ods csv file = '\class.csv';
proc report data = sashelp.class nowd;
 define _all_ / display ' ';
run;
ods csv close;

Sample log

1    ods csv file = '\class.csv';
NOTE: Writing CSV Body file: \class.csv
2    proc report data = sashelp.class nowd;
3     define _all_ / display ' ';
4    run;

NOTE: There were 19 observations read from the data set SASHELP.CLASS.
NOTE: PROCEDURE REPORT used (Total process time):
      real time           0.84 seconds
      cpu time            0.01 seconds

Sample output file

"Alfred","M",14,69,112.5
"Alice","F",13,56.5,84
"Barbara","F",13,65.3,98
"Carol","F",14,62.8,102.5
"Henry","M",14,63.5,102.5
"James","M",12,57.3,83
"Jane","F",12,59.8,84.5
"Janet","F",15,62.5,112.5
"Jeffrey","M",13,62.5,84
"John","M",12,59,99.5
"Joyce","F",11,51.3,50.5
"Judy","F",14,64.3,90
"Louise","F",12,56.3,77
"Mary","F",15,66.5,112
"Philip","M",16,72,150
"Robert","M",12,64.8,128
"Ronald","M",15,67,133
"Thomas","M",11,57.5,85
"William","M",15,66.5,112

[edit] The %ds2csv SAS Institute utility macro

This utility macro can be used to create a csv (or any type of delimited file). There are a number of options that can be used to customize the file created, including:

no column headers at all
variable names as the column headers
variable labels as the column headers

For example, the following code will create a CSV file with no column headers:

%DS2CSV(csvfile=\class.csv,
        data=sashelp.class,
        colhead=N,
        runmode=B
       )

which produces the following SAS Log Output:

1    %DS2CSV(csvfile=\class.csv,
2            data=sashelp.class,
3            colhead=N,
4            runmode=B
5           )
NOTE: CSV file successfully generated for SASHELP.CLASS

and the following file:

"Alfred","M","14","69","112.5"
"Alice","F","13","56.5","84"
"Barbara","F","13","65.3","98"
"Carol","F","14","62.8","102.5"
"Henry","M","14","63.5","102.5"
"James","M","12","57.3","83"
"Jane","F","12","59.8","84.5"
"Janet","F","15","62.5","112.5"
"Jeffrey","M","13","62.5","84"
"John","M","12","59","99.5"
"Joyce","F","11","51.3","50.5"
"Judy","F","14","64.3","90"
"Louise","F","12","56.3","77"
"Mary","F","15","66.5","112"
"Philip","M","16","72","150"
"Robert","M","12","64.8","128"
"Ronald","M","15","67","133"
"Thomas","M","11","57.5","85"
"William","M","15","66.5","112"

Check the online doc for details about these parameter settings and other available options.

Retrieved from "http://www.sascommunity.org/wiki/Create_a_CSV_file_without_column_names/headers_in_row_1"

Categories: EXPORT | Output Delivery System | REPORT

Create a CSV file without column names/headers in row 1

From sasCommunity

Contents

[edit] Run PROC EXPORT and recall and edit the code

[edit] Run PROC EXPORT and use a DATA step to rewrite the file without the first row

[edit] DATA _NULL_ with a PUT statement

[edit] DATA _NULL_ with a PUT statement, all fields quoted

[edit] ODS CSV and PROC REPORT with suppressed column headers

[edit] The %ds2csv SAS Institute utility macro

Views

Personal tools

Search

Main Menu

Page Activity

Toolbox