referencing iteraton char variable with do loop

Accepted Solution Solved
Reply
Contributor
Posts: 42
Accepted Solution

referencing iteraton char variable with do loop

 

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é

 


Accepted Solutions
Solution
‎02-17-2017 02:21 PM
Contributor
Posts: 42

Re: referencing iteraton char variable with do loop

[ Edited ]
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 Smiley Happy Thanks a lot again

View solution in original post


All Replies
Super User
Posts: 7,405

Re: referencing iteraton char variable with do loop

Since you do not create the macro variable &i anywhere, the reference to it won't work.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 42

Re: referencing iteraton char variable with do loop

Do you have some example how to create it??

Contributor
Posts: 42

Re: referencing iteraton char variable with do loop

Do you have any suggestion on this??

I will really appreciate

Super User
Posts: 11,114

Re: referencing iteraton char variable with do loop

[ Edited ]

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

Solution
‎02-17-2017 02:21 PM
Contributor
Posts: 42

Re: referencing iteraton char variable with do loop

[ Edited ]
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 Smiley Happy Thanks a lot again

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 146 views
  • 0 likes
  • 3 in conversation