topic Re: Loop over files for cimport and export in New SAS User

Loop over files for cimport and export

jsaldain33 — Sat, 21 Dec 2019 00:04:31 GMT

I am running the following code:

libname sasfiles 'C:\Users\Joaquin\Desktop';
filename intrans 'C:\Users\Joaquin\Desktop\zipcode_q1_2005.cpt' ;
proc cimport infile=intrans library=sasfiles;
run;

proc export data=sasfiles.zipcode_q1_2005
    outfile='C:\Users\Joaquin\Desktop\zipcode_q1_2005.csv'
    dbms=csv
    replace;
run;

I need to do this for many files (zipcode_q1_2005). At the end, for each cpt I have a csv file.

Re: Loop over files for cimport and export

Reeza — Sun, 22 Dec 2019 02:04:39 GMT

Convert it to a macro and run it for each file needed.

https://github.com/statgeek/SAS-Tutorials/blob/master/Turning%20a%20program%20into%20a%20macro.md

@jsaldain33 wrote:

I am running the following code:
libname sasfiles 'C:\Users\Joaquin\Desktop';
filename intrans 'C:\Users\Joaquin\Desktop\zipcode_q1_2005.cpt' ;
proc cimport infile=intrans library=sasfiles;
run;

proc export data=sasfiles.zipcode_q1_2005
    outfile='C:\Users\Joaquin\Desktop\zipcode_q1_2005.csv'
    dbms=csv
    replace;
run;
I need to do this for many files (zipcode_q1_2005). At the end, for each cpt I have a csv file.

Re: Loop over files for cimport and export

Tom — Sun, 22 Dec 2019 23:07:21 GMT

Do you have a list of the CPORT files? Do you have the list in a dataset?

Are you certain that each CPORT file only contains one dataset? Do any of them contain multiple datasets? Or other SAS objects, like format catalogs? When there is only one dataset are you sure the name of the dataset matches the name of the CPORT file?

Assuming that each file has one and only one dataset and you have the list in a variable named FILENAME in a dataset named FILELIST you could build a macro and then call the macro once for file.

%macro convert_one
(infile=
,outdir=
,outfile=
,libref=sasfiles
,memname=
);
%if not %length(&memname) %then %let memname=%scan(&infile,-2,'./"\');
%if not %length(&outfile) %then %let outfile=&memname..csv;
filename intrans "&infile";
proc cimport infile=intrans library=&libref;
run;
proc export data=&libref..&memname file="&outdir/&outfile" dbms=csv; 
run;
%mend convert_one;

Then your program to actually convert all of the files can just be generated from the list of files. For example by using a data step and CALL EXECUTE() function.

libname sasfiles 'C:\Users\Joaquin\Desktop';
%let outdir=C:\Users\Joaquin\Desktop\;

data _null_;
  set filelist ;
  call execute(cats('%nrstr(%convert_one)('
  ,'infile=',filename
  ,'outdir=',"&outdir"
  ,')'));
run;