topic Re: Utility to produce consistent column order CSV file in SAS Programming

Utility to produce consistent column order CSV file

Tom — Tue, 19 Jan 2016 18:21:24 GMT

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.

Re: Utility to produce consistent column order CSV file

SASKiwi — Tue, 19 Jan 2016 18:32:33 GMT

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:

http://support.sas.com/documentation/cdl/en/lebaseutilref/64791/HTML/default/viewer.htm#n0yo3bszlrh0byn1j4fxh4ndei8u.htm

Re: Utility to produce consistent column order CSV file

Tom — Tue, 19 Jan 2016 19:25:54 GMT

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"

Re: Utility to produce consistent column order CSV file

Ksharp — Wed, 20 Jan 2016 01:35:25 GMT

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;