topic How do I sort and export multiple datasets at once? in SAS Procedures

How do I sort and export multiple datasets at once?

COLDORANGE — Thu, 16 Oct 2014 08:18:50 GMT

I have a multiple (n=1000) datasets in work library.

All these datasets have same variable. ( var1, var2, var3, var5, var6,..) and I want to sort by two variables.

These dataset name is 'a1' to 'a1000'.

I don't want to merge all datasets.

And I want to export all dataset to csv.

How could I...do this?

Can you help me?

Thank you

Re: How do I sort and export multiple datasets at once?

Kurt_Bremser — Thu, 16 Oct 2014 08:40:20 GMT

With dataset names like that, its is quite easy;

%macro sort_and_export;

%do i = 1 %to 1000;

proc sort data=work.a&i;

by var1 var2;

run;

data _null_;

set work.a&i;

file "/path/a&i..csv"; /* double period because of macro variable i */

put

var1

var2

var3

var4

...

varx

;

run;

%end;

%mend;

%sort_and_export;

Re: How do I sort and export multiple datasets at once?

RW9 — Thu, 16 Oct 2014 09:25:40 GMT

Wasn't sure from your post, do you want all data exported to one CSV or one CSV per each dataset? For one file you could try:

data _null_;

set sashelp.vtable (where=(libname="SASHELP"));

by libname;

if first.libname then call execute('data want; set ');

call execute(' '||strip(memname));

if last.libname then call execute('; run;

proc sort data=want;

by col1 col2;

run;

proc export data=want outfile="c:\somefile.csv" dbms=dlm;

delimiter=",";

run;');

run;

For multiple:

data _null_;

set sashelp.vtable (where=(libname="SASHELP"));

call execute('proc sort data='||strip(memname)||' out=tmp;

by col1 col2;

run;

proc export data=tmp outfile="c:\'||strip(memname)||'.csv" dbms=dlm;

delimiter=",";

run;');

run;

Re: How do I sort and export multiple datasets at once?

COLDORANGE — Thu, 16 Oct 2014 12:13:22 GMT

Thank you for your reply

each csv :smileygrin:

a1.dataset -> a1.csv

Could you make one excel file 1,000 sheet?

thanks

Re: How do I sort and export multiple datasets at once?

RW9 — Thu, 16 Oct 2014 13:33:15 GMT

Ah, then you are talking about two separate items. CSV is a continuous text file delimited variable wise by commas and line wise by line breaks. It has no concept of sheets.

To do what you are talking about I would suggest that you use the ods tagset.excelxp tech, like:

ods tagsets.excelxp file="c:\output_file.xlsx";

data _null_;

set sashelp.vtable (where=(libname="SASHELP"));

call execute('ods tagsets.excelxp options=(sheet_name="'||strip(memname)||'");

proc report data=sashelp.'||strip(memname)||' nowd split="^";

columns col1 col2 col3 col4;

define col1 / order "This is an ordered variable";

define col2 / order "This is my second order";

run;');

run;

ods tagsets.excelxp close;

So as before, get a list of datasets and use a datastep to generate the code for each dataset. Precede the datastep with the open excelxp tagset and close it afterwards. Then for each dataset change the sheet name, and proc report to output the data using the proc report's sort functionality.

Re: How do I sort and export multiple datasets at once?

jakarman — Thu, 16 Oct 2014 13:46:04 GMT

Or as you are using sas windows and having access pcfiles use the Libname to excel method.

Every sheet will become equivalent to a dataset. Just copy all of them a copying datasets.

Question: who is capable handling that many sheets?