topic Re: macros in New SAS User

macros

Naveen1111 — Fri, 09 Oct 2020 17:27:58 GMT

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

Re: macros

Reeza — Fri, 09 Oct 2020 17:36:41 GMT

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

Re: macros

Naveen1111 — Fri, 09 Oct 2020 17:47:45 GMT

getting an error...Libref MYDATA is not assigned

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

Re: macros

Reeza — Fri, 09 Oct 2020 17:50:51 GMT

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

Re: macros

Naveen1111 — Fri, 09 Oct 2020 17:59:11 GMT

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;

Re: macros

Reeza — Fri, 09 Oct 2020 18:03:01 GMT

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

Re: macros

Naveen1111 — Fri, 09 Oct 2020 18:23:06 GMT

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

Re: macros

Reeza — Fri, 09 Oct 2020 18:54:30 GMT

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.