Hello:
I have two Proc import programs below. I found the second is not working. Could someone let me know why?
The first working code --
PROC IMPORT OUT=CA_2011
DATAFILE= "\\Pathway\CA_2011.xlsx"
DBMS=EXCEL REPLACE;
RANGE="CAcode$";
GETNAMES=YES;
MIXED=NO;
SCANTEXT=YES;
USEDATE=YES;
SCANTIME=YES;
RUN;
The second NOT-working one --
PROC IMPORT OUT=CA_2011
DATAFILE="\\Pathway\CA_2011.xlsx"
DBMS=excel REPLACE;
GETNAMES=YES;
DATAROW=2;
GUESSINGROWS=MAX;
RUN;
Log error message --
126 PROC IMPORT OUT=CA_2011
127 DATAFILE="\\Pathway\CA_2011.xlsx"
128 DBMS=excel REPLACE;
NOTE: The previous statement has been deleted.
NOTE: The previous statement has been deleted.
129 GETNAMES=YES;
130 DATAROW=2;
-------
180
131 GUESSINGROWS=MAX;
------------
180
ERROR 180-322: Statement is not valid or it is used out of proper order.
132 RUN;
One thing I found out is that when I saved the file as CSV. I modified the code similar as the 2nd above. The code works. Is there some kind of pattern for this?
PROC IMPORT OUT=CA_2011
DATAFILE="\\Pathway\CA_2011.csv"
DBMS=csv REPLACE;
GETNAMES=YES;
DATAROW=2;
GUESSINGROWS=MAX;
RUN;
Different options are valid for different DBMS.
GuessingRows and DataRows are not valid for Excel, change it to DBMS= XLSX.
@ybz12003 wrote:
Hello:
I have two Proc import programs below. I found the second is not working. Could someone let me know why?
The first working code --
PROC IMPORT OUT=CA_2011 DATAFILE= "\\Pathway\CA_2011.xlsx" DBMS=EXCEL REPLACE; RANGE="CAcode$"; GETNAMES=YES; MIXED=NO; SCANTEXT=YES; USEDATE=YES; SCANTIME=YES; RUN;
The second NOT-working one --
PROC IMPORT OUT=CA_2011 DATAFILE="\\Pathway\CA_2011.xlsx" DBMS=excel REPLACE; GETNAMES=YES; DATAROW=2; GUESSINGROWS=MAX; RUN;
Log error message --
126 PROC IMPORT OUT=CA_2011
127 DATAFILE="\\Pathway\CA_2011.xlsx"
128 DBMS=excel REPLACE;
NOTE: The previous statement has been deleted.
NOTE: The previous statement has been deleted.
129 GETNAMES=YES;
130 DATAROW=2;
-------
180
131 GUESSINGROWS=MAX;
------------
180
ERROR 180-322: Statement is not valid or it is used out of proper order.
132 RUN;
Try this:
PROC IMPORT OUT=CA_2011
DATAFILE="\\Pathway\CA_2011.xlsx"
DBMS=xlsx REPLACE;
GETNAMES=YES;
RANGE = "Sheet1$A1:G3000";
GUESSINGROWS=3000;
RUN;
Replace Sheet1 with the name of your Excel sheet and the range with your values.
One more question. How I could limit the observations, such as 3000, while I use proc import? I use OBS option in Proc import, it didn't work.
AFAIK there isn't an option within PROC IMPORT.
You can try the OPTION specifically but remember to reset it.
*set to 300;
options obs=3000;
*reset to all;
options obs = max;
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.