topic Re: Macro for Proc Import Excel file xlsx in SAS Procedures

Macro for Proc Import Excel file xlsx

PDMAUL — Sun, 13 Nov 2016 23:24:46 GMT

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

Re: Macro for Proc Import Excel file xlsx

Reeza — Mon, 14 Nov 2016 00:17:04 GMT

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

Re: Macro for Proc Import Excel file xlsx

PDMAUL — Mon, 14 Nov 2016 00:22:52 GMT

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)

Re: Macro for Proc Import Excel file xlsx

Reeza — Mon, 14 Nov 2016 00:29:58 GMT

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

Try it without it.

Re: Macro for Proc Import Excel file xlsx

PDMAUL — Mon, 14 Nov 2016 00:40:17 GMT

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)

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

Re: Macro for Proc Import Excel file xlsx

owenwqp1 — Mon, 11 Dec 2017 14:26:47 GMT

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.

Re: Macro for Proc Import Excel file xlsx

owenwqp1 — Mon, 11 Dec 2017 14:39:29 GMT

%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

Re: Macro for Proc Import Excel file xlsx

owenwqp1 — Mon, 11 Dec 2017 14:49:14 GMT

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!