GUESSINGROWS ( EXCEL to SAS) ??

[SAS83]

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;

[Reeza · Accepted solution]

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]

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]

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]

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]

Thanks for the suggestion...

[christinagting0]

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]

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]

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]

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]

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]

Mixed= does not appear to apply to DBMS=EXCELCS in V9.4M3