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.
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.
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.
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.
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'
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.
PS from the documentation for the GUESSINGROWS statement:
Interaction: | The GUESSINGROWS statement is valid only for delimited files. |
(as always: see Maxim 1)
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;
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
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: