BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Jcorti
Obsidian | Level 7

 

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é

 

1 ACCEPTED SOLUTION

Accepted Solutions
Jcorti
Obsidian | Level 7
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

View solution in original post

5 REPLIES 5
Jcorti
Obsidian | Level 7

Do you have some example how to create it??

Jcorti
Obsidian | Level 7

Do you have any suggestion on this??

I will really appreciate

ballardw
Super User

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

Jcorti
Obsidian | Level 7
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

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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