BookmarkSubscribeRSS Feed
ybz12003
Rhodochrosite | Level 12

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;

5 REPLIES 5
ybz12003
Rhodochrosite | Level 12

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;

 

 

Reeza
Super User

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;


 

alex_a
Fluorite | Level 6

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.

 

ybz12003
Rhodochrosite | Level 12

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.

Reeza
Super User

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;

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!

How to Concatenate Values

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.

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
  • 5 replies
  • 1029 views
  • 0 likes
  • 3 in conversation