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
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;
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;
Thank you for your reply
each csv :smileygrin:
a1.dataset -> a1.csv
Could you make one excel file 1,000 sheet?
thanks
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.
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?
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.