Hi all,
I want to write a macro which exports all sas data files in a folder to a .csv files.
Thanks for the help from all of you in this community, I could successfully imports several files in a folder using macro.
But this time, I have to export multiple files in a library as a .csv files.
I tried to change the macro for importing to the macro exporting, but it did not work well.
I would like to generate the list of .sas7bdat files in a folder and use a macro that can export all of them at the same time.
I am using Linux and the list of filenames is..
/data/jhhuh/panel/a/coffe/coffe_mk.sas7bdat
/data/jhhuh/panel/a/coffe/coffe_ik.sas7bdat
/data/jhhuh/panel/a/coffe/coffe_ae.sas7bdat
data/jhhuh/panel/a/coffe/redu.sas7bdat
......
The macro that I am considering is..
%macro data;
%let subdir=/data/jhhuh/panle/a/coffe/;
filename dir "&subdir.*.sas7bdat";
data new;
length filename fname $ 200;
infile dir eof=last filename=fname ;
input;
last: filename=fname;
run;
proc sort data=new nodupkey;
by filename;
run;
proc print data=new;
run;
data x;
set new end=last;
call symputx(cats('filename',_n_),filename);
call symputx(cats('dsn',_n_),compress(scan(filename,-1,'/'),'.KP'));
if last then call symputx('nobs',_n_);
run;
%macro readin;
%do i=1 %to &nobs;
proc export data=&&dsn&i outfile='&&filename&i/&&dsn&i.csv'
dbms=csv replace;
%end;
run;
%end;
%mend readin;
%readin;
%mend data;
%data;
I think that the bold parts have some problems...
Can anyone help me figure out how I can fix this problem?
Or, if you think that this code has many problems and you would like to suggest other code, please let me know.
Thank you for your help in advance!
First take a look at your FILENAME statement. I do not know Linux so do not know if the * will sufficiently concatenate the files - if not there are other ways. But at least there is a missing /.
filename dir "&subdir./*.sas7bdat";
have you verified that the data set NEW contains the list? What are its variable values?
Hi ,
here i'm sending sample code by trating all the datafiles in a folder as library in windows envornment.
%macro write_to_csv;
proc sql;
select memname into :dnames separated by ' ' from dictionary.tables where libname='WORK' and memtype='DATA';
select count(*) into :counts from dictionary.tables where libname='WORK' and memtype='DATA';
quit;
%do i= 1 %to &counts ;
%let dname=%scan(&dnames,&i,' ' );
ods csvall file="c:\test\&dname..csv";
proc print data=&dname noobs;
run;
ods csvall close;
%end;
%mend ;
%write_to_csv;
When working with SAS files use SAS tools.
You can gen the code you need with a data step and sashelp.vmember. You will use a different LIBNAME and the other parts of thw WHERE statement will be different. Also OUTFILE will need to be specific to your OS and desired path. I have UNIX and just wrote to my HOME.
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 how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.