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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.