specific excel sheets from an excel file and append them using a macro in SAS

[eroolpal]

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;

[Kurt_Bremser]

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]

• 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;

---