topic Re: How do I Import an excel file using macro statement in SAS Programming

How do I Import an excel file using macro statement

boodaloo1 — Sun, 10 Jul 2016 18:26:04 GMT

I have to create a macro statement to extract an excel file from my computer. I have the following code:

%macro one (a, b, c);

proc import out= &a

datafile= "\\Client\H&\Desktop\&b";

dbms=xlsx replace;

getnames=yes;

run;

proc sort data=&a;

by &c;

run;

%mend one;

%one (data1, datafileone.xlsx, id);

%one (data2, datafiletwo.xlsx, id);

%one (data3, datafilethree.xlsx, id);

I get the following errors:

ERROR 180-322: Statement is not valid or it is used out of proper order. (regarding the dbms=xlsx replace statement)

My computer then tells me that my files don't exist, which I am assuming is because the statement above is not working properly.

Does anyone have any advice on how to fix this problem?

Thanks!

Reeza — Sun, 10 Jul 2016 19:02:57 GMT

Always test your code outside of a macro loop.

%let a=data1;
%let b=datafileone.xlsx;


proc import out= &a
datafile= "\\Client\H&\Desktop\&b";
dbms=xlsx replace;
getnames=yes;
run;

In this case your semicolon is too early, you don't need the one after the datafile statement.

%let a=data1;
%let b=datafileone.xlsx;


proc import out= &a
datafile= "\\Client\H&\Desktop\&b"
dbms=xlsx replace;
getnames=yes;
run;

Reeza — Sun, 10 Jul 2016 19:03:55 GMT

You have H& in the filepath. If the path actually has a & it may generate a warning, but should still work.

Ideally you don't want that symbol in your path.

boodaloo1 — Sun, 10 Jul 2016 19:20:57 GMT

THANK YOU THANK YOU!! This fixed everything.