DATA Step, Macro, Functions and more

Proc Import Q?

Reply
Super Contributor
Posts: 374

Proc Import Q?

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;

Super Contributor
Posts: 374

Re: Proc Import Q?

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;

 

 

Super User
Posts: 21,546

Re: Proc Import Q?

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;


 

New Contributor
Posts: 2

Re: Proc Import Q?

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.

 

Super Contributor
Posts: 374

Re: Proc Import Q?

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.

Super User
Posts: 21,546

Re: Proc Import Q?

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;
Ask a Question
Discussion stats
  • 5 replies
  • 87 views
  • 0 likes
  • 3 in conversation