DATA Step, Macro, Functions and more

How do I Import an excel file using macro statement

Reply
Occasional Contributor
Posts: 6

How do I Import an excel file using macro statement

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!

 

Super User
Posts: 19,855

Re: How do I Import an excel file using macro statement

Posted in reply to boodaloo1

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;
Occasional Contributor
Posts: 6

Re: How do I Import an excel file using macro statement

THANK YOU THANK YOU!! This fixed everything. 

Super User
Posts: 19,855

Re: How do I Import an excel file using macro statement

Posted in reply to boodaloo1

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. 

Ask a Question
Discussion stats
  • 3 replies
  • 532 views
  • 0 likes
  • 2 in conversation