BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
SAS83
Fluorite | Level 6
Guessing rows in PROC IMPORT. I know that guesssingrows in the below code works for CSV,TAB,DLM files. But if I use same code to import excel but it won't work with guessingrows=100. Can any one have better method other than inserting extra rows at the top and put char or numeric so that SAS understands which format to take?

PROC IMPORT OUT= WORK.XXX
DATAFILE= "C:\Documents and Settings\YYY.csv"
DBMS=EXCEL REPLACE;
RANGE="Sheet1$";
GETNAMES=YES;
MIXED=NO;
GUESSINGROWS=40;
SCANTEXT=YES;
USEDATE=YES;
SCANTIME=YES;
RUN;
1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

The GUESSINGROWS statement applies to text/delimited files only, not Excel.

 

If using DBMS=EXCEL or EXCELCS, you can try the Mixed=Yes option. Mixed=YES will examine columns that contain both text and numbers and set them to character type.

Or export to CSV and use a data step to read the file, where you have complete control.

View solution in original post

11 REPLIES 11
Reeza
Super User

The GUESSINGROWS statement applies to text/delimited files only, not Excel.

 

If using DBMS=EXCEL or EXCELCS, you can try the Mixed=Yes option. Mixed=YES will examine columns that contain both text and numbers and set them to character type.

Or export to CSV and use a data step to read the file, where you have complete control.

SAS83
Fluorite | Level 6
I did check MIxed=Yes option but have not tried diffient possibilities. Like, if first 15 rows has char data and after it is numeric, then I need to set that variable as Numeric instead of Char. So, will this issue can be solved using MIXED=YES,??

SAS normally checks first 20 lins and then decides the charector or numeric format.. So, if mixed=yes will consider more than 20? like it check the full column to decide??
Reeza
Super User
A column in SAS can be only character or numeric, not both.

Mixed will set columns that contain both text and numbers to characters.

I don't understand this statement:
If first 15 rows has char data and after it is numeric, then I need to set that variable as numeric.

Are the character variables incorrect then or would you still like to see them? If you still want to see them then the column must be character.
If its incorrect data then import as character anyways and then clean your data in a data step to have an audit trail of whats done rather than rely on SAS to automically clean the data in the import statement.

You can check the documentation here:
http://support.sas.com/documentation/cdl/en/acpcref/63184/HTML/default/viewer.htm#a003103761.htm The documentation also had details on the guessing row option.

Message was edited by: Reeza
SAS83
Fluorite | Level 6
Thanks again. I was thinking that mixed=yes will set the columns with both text and numbers to charecter's and also both char and number to numeric, but it is not. Your statement made it clear now.

Thank you so much.
SAS83
Fluorite | Level 6
Thanks for the suggestion...
christinagting0
Quartz | Level 8

Did you ever find a solution to this? i'm having the exact same problem and I'm not sure Reeza's answer solved it??

Doc_Duke
Rhodochrosite | Level 12

Chris,

 

Please start a new thread and state your problem.  Reeza answered SAS83's question so saying you have the 'exact same problem' is not informative.

christinagting0
Quartz | Level 8

It is informative because it is THE EXACT SAME PROBLEM.

 

I can defintiely start a new thread though and increase redundancy and duplication on this site. 

 

 

bnawrocki
Quartz | Level 8

christinagting0: Did you figure out how to handle this? If I am understading your issue correctly, I had the same problem, too -- PROC IMPORT was reading Excel data, and not setting the length for a SAS character column long enough to store the entire string.

 

In my case, I solved it by setting the value of GuessingRows. By default, GuessingRows is 20, which means SAS uses the first 20 rows of the Excel spreadsheet to determine whether the data within a column is character or numeric, and what its length should be. If you have a longer character string in the 100th row, say, than in any of the first 20 rows for a column in Excel, SAS will not set the character length long enough to store it in SAS, and it will be truncated.

 

Unfortunately, you don’t enter the GUESSINGROWS statement into the program. You enter it in your own SAS registry, within SAS 9.4 itself. You only need to do this once.

 

So, open SAS 9.4, and in the command line – in the upper left corner of the screen – type regedit. Then navigate to Products-->BASE-->EFI-->GuessingRows and set the value to the number of rows in the CSV or Excel file you want SAS to scan to find the longest value. I chose 1000, because I knew I wouldn't have more than 1000 rows in an Excel file, but you could increase it up to 2147483647 (or MAX), although SAS documentation recommends not to, as it might adversely affect performance. I saw no performance hit ater I changed it to 1000.

 

christinagting0
Quartz | Level 8

Thanks so much! This was very informative.

 

I don't think I ever solved it..I just changed the number of lines the guessing rows would read manually in the code and kept it as a CSV file. 

 

So fo example I did it like this in my code:

 

proc import out=test
	datafile='C:example.csv'
	dbms=csv replace;
	*Optional statements are below; 
	getnames=yes; 
	guessingrows=1538;
run;
PerryNoble
Fluorite | Level 6
Mixed= does not appear to apply to DBMS=EXCELCS in V9.4M3

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 11 replies
  • 97339 views
  • 11 likes
  • 6 in conversation