BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
PDMAUL
Fluorite | Level 6

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

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

 

Try it without it. 

View solution in original post

7 REPLIES 7
Reeza
Super User

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);

 

PDMAUL
Fluorite | Level 6

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)

Reeza
Super User

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

 

Try it without it. 

PDMAUL
Fluorite | Level 6

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!!!.

 

owenwqp1
Obsidian | Level 7
I have the same problem! When I import a individual xlsx file, it works well. But when I copy the import code importing individual file to macro, sas gives the same error message as you provide.
owenwqp1
Obsidian | Level 7
%macro sic; %do i=1999 %to 2011; PROC IMPORT OUT= WORK.digit_&i DATAFILE= "F:\statistics\digit_&i" DBMS=xlsx REPLACE; sheet="fdi_2digiit_&i"; getnames=yes; RUN; %end; %mend; %sic
owenwqp1
Obsidian | Level 7
Hi PDMAUL, There is something wrong with my browser, and the post editor features are not working on my computer. So I attached a text doc of my code importing xlsx files with macro. Though not perfect, it works. Good luck!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 4540 views
  • 2 likes
  • 3 in conversation