macros

[Naveen1111]

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

[Reeza]

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]

getting an error...Libref MYDATA is not assigned

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

[Reeza]

Try a different engine, ie libname mydata xls 'path to file'
or PC FILES.

[Naveen1111]

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]

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]

I m sorry it's not working as getting the same error.

[Reeza]

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.