How do I Import an excel file using macro statement

[boodaloo1]

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]

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;

[boodaloo1]

THANK YOU THANK YOU!! This fixed everything. 

[Reeza]

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.