Does anyone have a simple utility to consistently produce CSV file from a SAS dataset. In particular I am looking for methods that would allow the column order in the CSV file to be consistent, even if the source SAS dataset has the columns defined in a different order. Perhaps something with a metadata file that describes the fields and included the column order.
I think SAS's DS2CSV macro may do this for you as you can specify a list of variables that both orders and selects the columns in the CSV:
Thanks.
So now I just need a reasonable method to generate the variable names in the proper order.
Here is one way that I thought of.
If I had metadata that listed variables in order for each target file. Perhaps something like this:
data data_dictionary;
infile cards truncover ;
input table :$32. name :$32. @ ;
do varnum=1 by 1 until (name=' ');
output;
input name :$32. @;
end;
cards;
TABLE1 name sex age height weight
TABLE2 name age height weight sex
;;;;
Then I could create a wrapper for the %DS2CSV macro that let the user specify the input dataset, output CSV file and which standard table it represents.
%macro standard_csv
(data= /* Input Dataset name */
,csvfile= /* Output CSV file name */
,table= /* Standard Table name */.
,metadata=data_dictionary
);
%local varlist ;
proc sql noprint ;
select name into :varlist separated by ' '
from &metadata
where upcase(table) = %upcase("&table")
order by varnum
;
quit;
%ds2csv (data=&data, runmode=b, csvfile=&csvfile,var=&varlist)
%mend standard_csv;
So then if I call it with %standard_csv(data=sashelp.class,csvfile=file1.csv,table=TABLE2) I would get a file like this:
"Name","Age","Height","Weight","Sex"
"Alfred","14","69","112.5","M"
"Alice","13","56.5","84","F"
"Barbara","13","65.3","98","F"
"Carol","14","62.8","102.5","F"
"Henry","14","63.5","102.5","M"
"James","12","57.3","83","M"
"Jane","12","59.8","84.5","F"
"Janet","15","62.5","112.5","F"
"Jeffrey","13","62.5","84","M"
"John","12","59","99.5","M"
"Joyce","11","51.3","50.5","F"
"Judy","14","64.3","90","F"
"Louise","12","56.3","77","F"
"Mary","15","66.5","112","F"
"Philip","16","72","150","M"
"Robert","12","64.8","128","M"
"Ronald","15","67","133","M"
"Thomas","11","57.5","85","M"
"William","15","66.5","112","M"
Why not creat a VIEW, it didn't cost you anything ?
proc sql;
create view standard as
select weight,name,age
from sashelp.class;
quit;
proc export data=standard outfile='/folders/myfolders/x.csv' dbms=csv replace;run;
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.