BookmarkSubscribeRSS Feed
boodaloo1
Fluorite | Level 6

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!

 

3 REPLIES 3
Reeza
Super User

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
Fluorite | Level 6

THANK YOU THANK YOU!! This fixed everything. 

Reeza
Super User

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. 

hackathon24-white-horiz.png

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 6648 views
  • 2 likes
  • 2 in conversation