topic Proc Import Q? in SAS Programming

Proc Import Q?

ybz12003 — Fri, 09 Feb 2018 16:27:25 GMT

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.

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;

Re: Proc Import Q?

ybz12003 — Fri, 09 Feb 2018 16:33:53 GMT

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;

Re: Proc Import Q?

Reeza — Fri, 09 Feb 2018 16:38:20 GMT

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;

Re: Proc Import Q?

ybz12003 — Fri, 09 Feb 2018 19:31:52 GMT

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.

Re: Proc Import Q?

Reeza — Fri, 09 Feb 2018 20:03:47 GMT

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;

Re: Proc Import Q?

alex_a — Fri, 09 Feb 2018 20:43:52 GMT

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.