BookmarkSubscribeRSS Feed
basabi
Calcite | Level 5

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);

 

 

 

7 REPLIES 7
Kurt_Bremser
Super User

%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?

Shmuel
Garnet | Level 18

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);

 

 

 

               

Shmuel
Garnet | Level 18

you may need amend your %import macro

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

basabi
Calcite | Level 5
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. -##
Reeza
Super User

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;

Shmuel
Garnet | Level 18

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 */

ballardw
Super User

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 13465 views
  • 3 likes
  • 5 in conversation