Help using Base SAS procedures

Macro for Proc Import Excel file xlsx

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

Macro for Proc Import Excel file xlsx

Hello all,

 

I am having an issue with trying to use proc import in a macro for an .xlsx Excel file. Below is my code and the error message from the log.

 

60 %macro pharma (a,b);
61 proc import out = &a
62 datafile = "C:\SUM_2016\SUM2016_HW\project2\&b"
63 dbms = xlsx replace;
64 getnames = yes;
65 mixed = yes;
66 datarow = 2;
67 run;
68 %mend pharma;
69 %pharma (project2, project2.xlsx)
NOTE: The previous statement has been deleted.
NOTE: The previous statement has been deleted.
NOTE: Line generated by the invoked macro "PHARMA".
1 getnames = yes; mixed = yes; datarow = 2; run;

-----

180

ERROR 180-322: Statement is not valid or it is used out of proper order.

 

My question is, why is SAS telling me getnames=yes; mixed=yes; datarow=2;  is not valid or is used out of proper oder? Can you not use these statements when importing a .xlsx file?

 

When I take out getnames=yes; mixed=yes; datarow=2 and run the program, it runs correctly with the below log. Any help in understanding this will be very much appreciated.

 

53 %macro pharma (a,b);
54 proc import out = &a
55 datafile = "C:\SUM_2016\SUM2016_HW\project2\&b"
56 dbms = xlsx replace;
57 run;
58 %mend pharma;
59 %pharma (project2, project2.xlsx)

NOTE: The import data set has 5 observations and 27 variables.
NOTE: WORK.PROJECT2 data set was successfully created.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 0.86 seconds
cpu time 0.04 seconds


Accepted Solutions
Solution
‎11-13-2016 07:40 PM
Super User
Posts: 17,819

Re: Macro for Proc Import Excel file xlsx

I don't think DATAROW is supported for DBMS=XLSX. 

 

Try it without it. 

View solution in original post


All Replies
Super User
Posts: 17,819

Re: Macro for Proc Import Excel file xlsx

Please post your code, the log can change things. 

 

Also, try using the MPRINT/SYMBOLGEN options to see what happens.

 

options mprint symbolgen;

%pharma (project2, project2.xlsx);

 

New Contributor
Posts: 3

Re: Macro for Proc Import Excel file xlsx

Hello Reeza,

 

Here is my code with the getnames=yes; mixed=yes; datarow=2. For some reason, it does not run with those statements. But when I take them out, the program runs correctly. This is my first time importing a .xlsx file so i'm not sure how different it is from importing a .xls file and the options that can be used within the proc import step.

 

 

%macro pharma (a,b);
proc import out = &a
datafile = "C:\SUM_2016\SUM2016_HW\project2\&b"
dbms = xlsx replace;

getnames = yes;

mixed = yes;

datarow = 2;
run;
%mend pharma;
%pharma (project2, project2.xlsx)

Solution
‎11-13-2016 07:40 PM
Super User
Posts: 17,819

Re: Macro for Proc Import Excel file xlsx

I don't think DATAROW is supported for DBMS=XLSX. 

 

Try it without it. 

New Contributor
Posts: 3

Re: Macro for Proc Import Excel file xlsx

I took datarow=2 and it still gave me the same error message. When I took out mixed=yes as well, the program ran correctly.

 

%macro pharma (a,b);
proc import out = &a
datafile = "C:\SUM_2016\SUM2016_HW\project2\&b"
dbms = xlsx replace;
getnames=yes;
run;
%mend pharma;
%pharma (project2, project2.xlsx)

 

 

Log:


10 %macro pharma (a,b);
11 proc import out = &a
12 datafile = "C:\SUM_2016\SUM2016_HW\project2\&b"
13 dbms = xlsx replace;
14 getnames=yes;
15 run;
16 %mend pharma;
17 %pharma (project2, project2.xlsx)

NOTE: The import data set has 5 observations and 27 variables.
NOTE: WORK.PROJECT2 data set was successfully created.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 0.03 seconds
cpu time 0.03 seconds

 

So I guess datarow=<opt> and mixed=<yes/no> are not supported for dbms=xlsx. That is interesting. I will take note of that in my lecture notes for class. Thank you for helping me Reeza!!!.

 

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 621 views
  • 2 likes
  • 2 in conversation