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?
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.