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!
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;
THANK YOU THANK YOU!! This fixed everything.
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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.