topic Re: Macro for exporting files in a folder in SAS Programming

Macro for exporting files in a folder

jhhuh — Wed, 03 Apr 2013 02:59:38 GMT

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!

Re: Macro for exporting files in a folder

ArtC — Wed, 03 Apr 2013 06:40:17 GMT

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?

Re: Macro for exporting files in a folder

AjayaMuchalame — Wed, 03 Apr 2013 08:03:56 GMT

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;

Re: Macro for exporting files in a folder

data_null__ — Wed, 03 Apr 2013 11:03:25 GMT

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.

filename FT71F003 temp; 
data _null_; 
   file FT71F003;
   set sashelp.vmember(keep=libname memname memtype);
   where libname eq 'SASHELP' and memtype eq 'DATA' and memname eq: 'T'; 
   data = catx('.',libname,memname);
   outfile = catx('/','~',catx('.',memname,'csv'));
   put 'proc export ' data=  outfile=:$quote128. 'replace dbms=csv; run;'; 
   run; 
 %inc FT71F003;