BookmarkSubscribeRSS Feed
Naveen1111
Calcite | Level 5

Hi,

Can someone tell me if i want to import multiple sheets through macros by using loop how can I do that?

Below is the mentioned program, I tried but working with the same name sheet i.e.(sheet1, sheet2, sheet3, and so on). What if the sheet name in excel is different (navin, pravin, sachin, abc...)

 

%macro aml(a=);
proc import
datafile="D:\SAS\3. SAS classes clinical\DAY6 7\mydata1.xls"
out=&a
DBMS=xls
replace;
sheet="&a";
getnames=yes;
run;
%mend aml;

 

%macro lop;
data _null_;
%do i=1 %to 2;
%aml(a=sheet&i);
%end;
run;
%mend lop;

 

thanks in advance

Regards,

Navin Dhawan

7 REPLIES 7
Reeza
Super User

It's easier to use a libname for copying over the files in my experience. The select statement is optional, if you do not specify it, all sheets will be copied over.

 

libname mydata excel 'D:\SAS\3. SAS classes clinical\DAY6 7\mydata1.xls';

proc copy in=myData out=work;
select Sheet1 Sheet2;
run;

@Naveen1111 wrote:

Hi,

Can someone tell me if i want to import multiple sheets through macros by using loop how can I do that?

Below is the mentioned program, I tried but working with the same name sheet i.e.(sheet1, sheet2, sheet3, and so on). What if the sheet name in excel is different (navin, pravin, sachin, abc...)

 

%macro aml(a=);
proc import
datafile="D:\SAS\3. SAS classes clinical\DAY6 7\mydata1.xls"
out=&a
DBMS=xls
replace;
sheet="&a";
getnames=yes;
run;
%mend aml;

 

%macro lop;
data _null_;
%do i=1 %to 2;
%aml(a=sheet&i);
%end;
run;
%mend lop;

 

thanks in advance

Regards,

Navin Dhawan


 

Naveen1111
Calcite | Level 5

getting an error...Libref MYDATA is not assigned

 

ERROR: Connect: Class not registered
ERROR: Error in the LIBNAME statement.

 

Reeza
Super User
Try a different engine, ie libname mydata xls 'path to file'
or PC FILES.
Naveen1111
Calcite | Level 5

thanks reeza last time u helped me a lot.

xlsx is the only engine, i tried with xls but getting the same error.

libname gg xlsx "C:\Users\abc\Desktop\SAS\mydata1.xlsx" ;
proc copy in=gg out=work;
run;

Reeza
Super User
Show a PROC IMPORT code to import the file that works. Whatever is the correct DBMS parameter should also work as a libname statement.

Naveen1111
Calcite | Level 5
I m sorry it's not working as getting the same error.
Reeza
Super User
I would check with your company then if your SAS installation is correct or if you don't have the license to import XLSX files.

It seems like you can't import any Excel files, figure that out first and then you can generalize it to work for all cases.

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 7 replies
  • 1196 views
  • 0 likes
  • 2 in conversation