topic Re: Import Files with Macro in SAS Programming

Import Files with Macro

basabi — Wed, 16 Nov 2016 08:05:05 GMT

i have a list of excel files to be imported and has to be consolidated in a big file.

the file name are in the format customer data file 2016-04,2016-03 and so on.

first i have tried

%MACRO import(filename);
proc import datafile="&&loc&filename "
out=cust%sysfunc(compress(&filename.))
dbms=excel replace;
run;
%MEND;

%import(2016-14);
proc print;run;

but i want to automate it further and thats where i am getting stuck

%let loc=C:\Users\user\Downloads\New folder\Customer Data File;

%MACRO importfile(filename);

%do i=1 to 60;
data _null_;

data files;
callsymput("filename",put("042016"d, yymm7.));
run;

%if filename=filexist("&&loc.&filename.xls");

proc import datafile="&&loc. &filename"
out=c%sysfunc(compress(&filename.))
dbms= excel replace;
run;

data final;
set final&filename. ;
run;
%end;

%MEND;

%importfile(04-2016);

Re: Import Files with Macro

Kurt_Bremser — Wed, 16 Nov 2016 08:07:34 GMT

%do i = 1 %to 60;

%do also needs a % with the "to".

You have filename as macro parameter, but set it explicitly in the data _null_, so the macro parameter is useless.

%if filename=filexist("&&loc.&filename.xls");

This would always be semantically wrong (filename is a string, the result of the function is boolean/numeric), but in a macro you can't use datastep functions without %sysfunc.

And there is no %then!

Since you never use i in the macro loop (or change anything), you would just repeat the same action 60 times.

What were you trying to achieve, anyway?

Re: SAS

Shmuel — Wed, 16 Nov 2016 08:10:28 GMT

Am I right -

you are trynig to import 60 months (5 years) files, each file name is in a format of yyyy-mm.

You need to define last (current) month to start from and loop backwards by month 60 times.

Then you want to append each month data to the previous acuumulated data, to get one consolidated table.

Try next code, using your macro %import as is:

%let loc=C:\Users\user\Downloads\New folder\Customer Data File; /* assumed full path name only */

%macro import_files(start=2016-04, libout=, cons_name=);

/* initiating the consolidated table */

proc datasets lib=&libout nolist; /* library for the consolidated table */

delete &cons_name; /* name for the consolidated table */

run; /* ignore message like cons_name does not exist */

%let fname = &start;

%do i=1 %to 3 ; /* 3 times just for test, then if OK change to 60 */

%let path_name = &loc.\&fname..xls; /* pay attention to changes done */

%if %sysfunc(filexist(path_name)) %then %do;

%import(&fname..xls);

proc append base=&cons_name data=cust&fname; run; /* output name as created by %import */

%end;

/* next fname is previous month */

data _NULL_;

datex = compress("&fname.01", '-');

date = input(datex,yymmdd8.);

prev_month = intnx('month',date,-1);

new_fname = catx('-',left(year(prev_month)) , put(month(prev_year),z2.));

call symput('fname', strip(new_name));

end;

%end;

%mend import_files;

%import_files(start=2016-04, libout=work, cons_name=cons_data);

Re: SAS

Shmuel — Wed, 16 Nov 2016 08:21:14 GMT

you may need amend your %import macro

proc import datafile="&loc.\&filename..xls"

Re: SAS

basabi — Wed, 16 Nov 2016 09:23:50 GMT

yes exactly..but i want to automate that out datafile name so that the last
part of it is attached with an "&i." so that it iterates60 times.

##- Please type your reply above this line. Simple formatting, no
attachments. -##

Re: SAS

Reeza — Wed, 16 Nov 2016 09:39:53 GMT

You have several good examples above. I wrote this answer a few days ago for DBF files but it can easily be modified for XLSX files, modify the proc import in the macro.

You would need to create a dataset with the filenames and output dataset name to pass to the macro but that's an easy step.

So a standard proc import would be:

proc import out=sample1 datafile="path to dbf file.dbf" dbms=DBF replace;
run;

The problem now, is how to generate this set of code for every file in your file list. Using the CALL EXECUTE statement from @Tom is your best bet. You call also create a small macro and call it for each filename, using CALL EXECUTE. If you're new to SAS this can be easier to understand.

*Create a macro that imports the DBF

%macro import_dbf(input= , output=);

proc import out=&out datafile="&output" dbms=DBF replace;
run;

%mend;

Then call macro from dataset. I'm naming the datasets DBF001, DBF0002 etc.

%let dirname=C:\_localdata;

data dirlist;
informat fname $20.;
input fname;
cards;
data1.dbf
data2.dbf
data3.dbf
data4.dbf
;
run;

data out;
set dirlist;
str=catt('%import_dbf(input="', "&dirname", '\', fname, '", output=dbf',
put(_n_, z4.), ');');

call execute(str);
run;

proc print data=out;
run;

Re: SAS

Shmuel — Wed, 16 Nov 2016 09:49:46 GMT

Your import macro is:

%MACRO import(filename);
proc import datafile="&loc.\&filename "
out=cust%sysfunc(compress(&filename.))
dbms=excel replace;
run;
%MEND;

then you need change one line:

out=cust&filename&i /* I think that %sysfunc(compress - are not needed */

Re: Import Files with Macro

ballardw — Wed, 16 Nov 2016 16:38:21 GMT

Be prepared to correct issues of variable type mismatches and different lengths of character variables.

Proc import relies on Excel to tell SAS the variable type and size and is notorious for what human's think of errors.