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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.