topic Re: referencing iteraton char variable with do loop in SAS Programming

referencing iteraton char variable with do loop

Jcorti — Fri, 17 Feb 2017 18:16:14 GMT

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é

Re: referencing iteraton char variable with do loop

Kurt_Bremser — Fri, 17 Feb 2017 18:30:24 GMT

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

Re: referencing iteraton char variable with do loop

Jcorti — Fri, 17 Feb 2017 18:31:40 GMT

Do you have some example how to create it??

Re: referencing iteraton char variable with do loop

Jcorti — Fri, 17 Feb 2017 18:59:23 GMT

Do you have any suggestion on this??

I will really appreciate

Re: referencing iteraton char variable with do loop

ballardw — Fri, 17 Feb 2017 20:49:34 GMT

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

Re: referencing iteraton char variable with do loop

Jcorti — Fri, 17 Feb 2017 19:24:29 GMT

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