A regularly recurring question on SAS-L is How to create a CSV file without column names/headers in row 1.
The following was 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). This was pulled from sasCommunity.org.
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.
Run PROC EXPORT with PUTNAMES=NO
Sample program
proc export data=data_to_export
outfile='C:\data_exported.csv'
dbms=csv
replace;
putnames=no;
run;
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
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
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"
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
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.
The CSV tagset and the table_headers="NO" option
Sample program by Richard A. DeVenezia
%let output_folder = %sysfunc(pathname(WORK));
ods listing close;
ods tagsets.csv
file="&output_folder.\class-noheader.csv"
options(table_headers="no")
;
proc print noobs data=sashelp.class;
where name < 'J';
run;
ods tagsets.csv close;
ods listing;
creates this output
"Alfred","M",14,69.0,112.5
"Alice","F",13,56.5,84.0
"Barbara","F",13,65.3,98.0
"Carol","F",14,62.8,102.5
"Henry","M",14,63.5,102.5
%put SYSSCPL=&SYSSCPL., SYSVLOG=&SYSVLONG;
===
SYSSCPL=W32_VSHOME, SYSVLOG=9.02.01M0P020508
This article was originally published by Don Henderson on sasCommunity.org.
