Proc import with space in sheet name - how to mention range?

[ilearnsas]

Hi

I am trying to import an excel file with a space in the sheet name. I researched online and found that we can put a 'Sheet Name'n 

However when I mention the range in the range= 'Sheet Name$A21:I49'  is not working

I tried 'Sheet Name$A21:I49'n and  'Sheet Name'n$A21:I49'

In other words below is not working near range:

proc import datafile=innfile out=test
dbms=csv replace;
sheet="Sheet Name$"n;
getnames=no;
guessingrows=20;
range="Sheet Name$A2134:I2499";
run;

Sheet option works fine but not range. How to fix this? 

Error: 180-322: Statement is not valid or it is used out of proper order.

[Reeza]

You need to use one of SHEET or RANGE, not both.
So if you use RANGE, remove the SHEET option. The SHEET option you're referencing is not for SHEET in PROC IMPORT, it's for importing data using a LIBNAME statement.

[ilearnsas]

If I manually rename the 'sheet name' to 'sheet_name' instead of space, then both sheet and range options work.

So for a file like this, lets assume I have a range, can I just do range range="Sheet Name$A2134:I2499"? Unfortunately space does not work. 

I tried below now and it does not work either:

proc import datafile=innfile out=Full_Hist
dbms=csv replace;
/*sheet="Sheet Name$"n;*/

/*Commented sheet option*/
getnames=no;
guessingrows=20;
range="Sheet Name$A2134:I2499";
run;

ERROR 180-322: Statement is not valid or it is used out of proper order.

[Reeza]

range="'Sheet Name'$A2134:I2499";

You need to us Excel notation, try the above format.

Otherwise, if this is one time or a template, consider using a named range.

https://blogs.sas.com/content/sasdummy/2018/06/21/read-excel-range/

---

@ilearnsas wrote:

If I manually rename the 'sheet name' to 'sheet_name' instead of space, then both sheet and range options work.

So for a file like this, lets assume I have a range, can I just do range range="Sheet Name$A2134:I2499"? Unfortunately space does not work. 

I tried below now and it does not work either:

proc import datafile=innfile out=Full_Hist
dbms=csv replace;
/*sheet="Sheet Name$"n;*/

/*Commented sheet option*/
getnames=no;
guessingrows=20;
range="Sheet Name$A2134:I2499";
run;

ERROR 180-322: Statement is not valid or it is used out of proper order.

---

[ilearnsas]

I tried this as well. range="'Sheet Name'$A2134:I2499" Gives the same error 

ERROR 180-322: Statement is not valid or it is used out of proper order.

[ilearnsas]

I cannot use the named range because I am using proc http automating the download and then using proc import to create a sas dataset.

I cannot rename the file name as well since this is an automated process.. 

Unless there is a way in sas to automatically rename the excel sheet from "sheet name" to 'Sheet_Name' 

[Kurt_Bremser]

Please copy/paste the whole log of the step into a window opened with the {i} button.

I suspect that the guessingrows= statement is the real culprit.

[Kurt_Bremser]

PS from the documentation for the GUESSINGROWS statement:

Interaction:

The GUESSINGROWS statement is valid only for delimited files.

(as always: see Maxim 1)

[ilearnsas]

I commented out guessingrows and still the same error.

Here is the log below:- 

25 GOPTIONS ACCESSIBLE;
26 options validvarname=ANY;
27 proc import datafile=innfile out=Full_Hist
28 /*(rename=(A=US =new_var2))*/
29 dbms=csv replace;
NOTE: The previous statement has been deleted.
30 /*sheet="Full History$"n;*/
31 getnames=no;
32 /*datarow=2134;*/
33 /*guessingrows=20;*/
34 range="'Full History'$A2134:I2499";
_____
180
ERROR 180-322: Statement is not valid or it is used out of proper order.

35 run;

NOTE: Unable to open parameter catalog: SASUSER.PARMS.PARMS.SLIST in update mode. Temporary parameter values will be saved to
WORK.PARMS.PARMS.SLIST.
36 /**********************************************************************
37 * PRODUCT: SAS
38 * VERSION: 9.4
39 * CREATOR: External File Interface
40 * DATE: 25SEP19
41 * DESC: Generated SAS Datastep Code
42 * TEMPLATE SOURCE: (None Specified.)
43 ***********************************************************************/
44 data WORK.FULL_HIST ;
45 %let _EFIERR_ = 0; /* set the ERROR detection macro variable */
46 infile INNFILE delimiter = ',' MISSOVER DSD ;
47 informat VAR1 $1427. ;
48 informat VAR2 $643. ;
49 informat VAR3 $362. ;
2 The SAS System 11:09 Wednesday, September 25, 2019

50 informat VAR4 $583. ;
51 informat VAR5 $198. ;
52 informat VAR6 $88. ;
53 informat VAR7 $4. ;
54 informat VAR8 $103. ;
55 informat VAR9 $601. ;
56 informat VAR10 $210. ;
57 informat VAR11 $616. ;
58 informat VAR12 $104. ;
59 informat VAR13 $4. ;
60 informat VAR14 $745. ;
61 format VAR1 $1427. ;
62 format VAR2 $643. ;
63 format VAR3 $362. ;
64 format VAR4 $583. ;
65 format VAR5 $198. ;
66 format VAR6 $88. ;
67 format VAR7 $4. ;
68 format VAR8 $103. ;
69 format VAR9 $601. ;
70 format VAR10 $210. ;
71 format VAR11 $616. ;
72 format VAR12 $104. ;
73 format VAR13 $4. ;
74 format VAR14 $745. ;
75 input
76 VAR1 $
77 VAR2 $
78 VAR3 $
79 VAR4 $
80 VAR5 $
81 VAR6 $
82 VAR7 $
83 VAR8 $
84 VAR9 $
85 VAR10 $
86 VAR11 $
87 VAR12 $
88 VAR13 $
89 VAR14 $
90 ;
91 if _ERROR_ then call symputx('_EFIERR_',1); /* set ERROR detection macro variable */
92 run;

[Kurt_Bremser]

A csv file does not have ranges.

[Reeza]

That's not the full log, I understand why you're trying to mask things but you didn't include the file statement.

Either way, as Kurt has correctly pointed out you have a CSV file, not an Excel file and they're handled differently.

Excel is good for viewing CSV files but it doesn't always render them correctly so its user beware for anything you see in Excel.

If you want to limit the rows/columns you need to import there are other ways to do so.

[ilearnsas]

What other option can be used here to let know sas to pick a few specific rows and not everything? i.e. instead of range option, can we use anything else 

[Reeza]

Well, you know the record numbers so you can use FIRSTOBS to start reading at a specific line and use OBS= to read only a specific number of records.

You can also use DROP/KEEP to keep/drop variables, but you have to read all variables first. You do not have to read all rows though.

[Vince_SAS]

Are you reading an Excel file or a CSV file?  Your code says CSV.

Like @Kurt_Bremser said, there are no sheets or ranges in a CSV file.

Vince DelGobbo

SAS R&D