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