How do I sort and export multiple datasets at once?

[COLDORANGE]

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

[Kurt_Bremser]

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;

[RW9]

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;

[COLDORANGE]

Thank you for your reply

each csv :smileygrin:

a1.dataset -> a1.csv

Could you make one excel file 1,000 sheet?

thanks

[RW9]

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.

[jakarman]

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?