11-16-2016 01:50 AM - last edited on 11-16-2016 03:05 AM by KurtBremser
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
proc import datafile="&&loc&filename "
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;
%do i=1 to 60;
proc import datafile="&&loc. &filename"
dbms= excel replace;
set final&filename. ;
11-16-2016 02:57 AM - edited 11-16-2016 03:07 AM
%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.
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?
11-16-2016 03:02 AM - edited 11-16-2016 03:10 AM
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;
proc append base=&cons_name data=cust&fname; run; /* output name as created by %import */
/* next fname is previous month */
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));
%import_files(start=2016-04, libout=work, cons_name=cons_data);
11-16-2016 04:23 AM
11-16-2016 04:39 AM
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;
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;
Then call macro from dataset. I'm naming the datasets DBF001, DBF0002 etc.
informat fname $20.;
str=catt('%import_dbf(input="', "&dirname", '\', fname, '", output=dbf',
put(_n_, z4.), ');');
proc print data=out;
11-16-2016 04:49 AM
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 */
11-16-2016 11:38 AM
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.