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.
Sample program
proc export data=data_to_export
outfile='C:\data_exported.csv'
dbms=csv
replace;
putnames=no;
run;
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
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
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"
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
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:
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.
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.
Is there a way to force the headers to be formatted with a specific case?
I.E. all uppercase or all lowercase
Sometimes there's a header and sometimes not.
Big difference! Why not mention this?
Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!
Data Literacy is for all, even absolute beginners. Jump on board with this free e-learning and boost your career prospects.