Hi all,
The reason why I am creating iteration char variables is because I want to reference them to multiple
excel spreadsheets obtained from a given excel file **highlighted in black** below:
I have 25 spreadsheets starting from M00_ACCT_BAL_AMT to M24_ACCT_BAL_AMT on DVR_V1_PROD_01182017.xls
file
%macro import_loop; %do i1=0 %to 24; i = put(i1,z2.); proc import out = import_&i datafile = '/location/DVR_V1_PROD_01182017.xls' dbms = xls ; sheet ="M&i._ACCT_BAL_AM"; getnames = yes; run; output; %end; %mend import_loop;
This is the error that I am getting -25 times on the same log --
Requested Sheet Name not found on Excel -> /location/DVR_V1_PROD_01182017.xls
Requested Input File Is Invalid
ERROR: Import unsuccessful. See SAS Log for details.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 0.06 seconds
cpu time 0.00 seconds
Somebody could help me on this?
Maybe I doing something wrong on do loop statement calling each excel spreadsheet
Thanks in advance,
José
options mprint mlogic symbolgen;
%macro import_loop;
%do i1=0 %to 24;
%let i = %sysfunc( putn(&i1,z2.));
proc import out = imported_&i
datafile = '/testfile/DVR/DVR_V1_PROD_01182017.xls'
dbms = xls ;
sheet ="M&i._ACCT_BAL_AMT";
getnames = yes;
run;
%end;
%mend import_loop;
%import_loop;
It worked man 🙂 Thanks a lot again
Since you do not create the macro variable &i anywhere, the reference to it won't work.
Do you have some example how to create it??
Do you have any suggestion on this??
I will really appreciate
instead of
i = put(i1,z2.);
try
%let i = %sysfunct( putn(&i1,z2.));
You should have some other error messages about the code:
i = put(i1,z2.); appearing outside of a datastep, though you may not have recognized them.
Learn to use Options mprint, mlogic and symbolgen when working with macros that have problems
options mprint mlogic symbolgen;
%macro import_loop;
%do i1=0 %to 24;
%let i = %sysfunc( putn(&i1,z2.));
proc import out = imported_&i
datafile = '/testfile/DVR/DVR_V1_PROD_01182017.xls'
dbms = xls ;
sheet ="M&i._ACCT_BAL_AMT";
getnames = yes;
run;
%end;
%mend import_loop;
%import_loop;
It worked man 🙂 Thanks a lot again
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.
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.