BookmarkSubscribeRSS Feed
eroolpal
Fluorite | Level 6

Hi I am trying to write a code where I can get specific sheets named a1, b1, c1 from and excel file and append them and output them as a SAS file. this is the code I have been trying but I am getting the following error "ERROR: Couldn't find sheet in spreadsheet". the code is as follows. 

 

proc datasets lib=data nolist;

delete table_append;
quit;
run;


%let varlist1= a1~b1~c1;

%macro testing2();
%do i1 = 1 %to %sysfunc(countw("&vallist1", "~"));
%let kk1 = %scan(&vallist1, &i1,~);
PROC IMPORT
OUT= testex
DATAFILE= "C:\location\cases.xlsx"
DBMS=xlsx
REPLACE;
SHEET="&kk1";
GETNAMES=YES;
RUN;
data aan1;
set testex;
run;
proc append base=data.table_append data= aan1 Force;
run;

%end;
%mend testing2;
%testing2;

2 REPLIES 2
Kurt_Bremser
Super User

Since the macro language knows only text as data type, no quotes are needed:

%do i1 = 1 %to %sysfunc(countw(&vallist1,~));

But I suspect your process will not be stable because of the vagaries of PROC IMPORT. You will be much better off saving the sheets to text files so you can read them all in one single data step.

Reeza
Super User

 

  • The macro variable is varlist1
  • The code references a macro variable vallist1
  • Formatting code helps for legibility and debugging
  • The data step is unnecessary
  • Need to delete the imported data between loops otherwise if the import fails, the old data is used
  • It helps to use descriptive names for things, ie kk1 can be sheetName
  • The macro variable should be an input to the macro not as a separate macro variable IMO (varlist).

 

%let varlist1= a1~b1~c1;

%macro testing2();
	%do i1=1 %to %sysfunc(countw(&varlist1, ~));
		%let sheetName = %scan(&varlist1, &i1, ~);

        %put &sheetname;
        
		PROC IMPORT OUT=testex DATAFILE="C:\location\cases.xlsx" DBMS=xlsx REPLACE;
			SHEET="&sheetName";
			GETNAMES=YES;
		RUN;


		proc append base=data.table_append data=testex Force;
		run;
		
		*delete table otherwise if proc import fails it will reload old data;
		
		proc sql;
		drop table testex;
		quit;

	%end;
%mend testing2;

%testing2;

@eroolpal wrote:

Hi I am trying to write a code where I can get specific sheets named a1, b1, c1 from and excel file and append them and output them as a SAS file. this is the code I have been trying but I am getting the following error "ERROR: Couldn't find sheet in spreadsheet". the code is as follows. 

 

proc datasets lib=data nolist;

delete table_append;
quit;
run;


%let varlist1= a1~b1~c1;

%macro testing2();
%do i1 = 1 %to %sysfunc(countw("&vallist1", "~"));
%let kk1 = %scan(&vallist1, &i1,~);
PROC IMPORT
OUT= testex
DATAFILE= "C:\location\cases.xlsx"
DBMS=xlsx
REPLACE;
SHEET="&kk1";
GETNAMES=YES;
RUN;
data aan1;
set testex;
run;
proc append base=data.table_append data= aan1 Force;
run;

%end;
%mend testing2;
%testing2;


 

 

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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
  • 2 replies
  • 450 views
  • 3 likes
  • 3 in conversation