Help using Base SAS procedures

GUESSINGROWS ( EXCEL to SAS) ??

Accepted Solution Solved
Reply
Contributor
Posts: 32
Accepted Solution

GUESSINGROWS ( EXCEL to SAS) ??

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;

Accepted Solutions
Solution
‎04-26-2017 08:40 AM
Super User
Posts: 17,775

Re: GUESSINGROWS ( EXCEL to SAS) ??

[ Edited ]

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


All Replies
Solution
‎04-26-2017 08:40 AM
Super User
Posts: 17,775

Re: GUESSINGROWS ( EXCEL to SAS) ??

[ Edited ]

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.

Contributor
Posts: 32

Re: GUESSINGROWS ( EXCEL to SAS) ??

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??
Super User
Posts: 17,775

Re: GUESSINGROWS ( EXCEL to SAS) ??

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
Contributor
Posts: 32

Re: GUESSINGROWS ( EXCEL to SAS) ??

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.
Contributor
Posts: 32

Re: GUESSINGROWS ( EXCEL to SAS) ??

Thanks for the suggestion...
Frequent Contributor
Posts: 88

Re: GUESSINGROWS ( EXCEL to SAS) ??

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??

Trusted Advisor
Posts: 2,113

Re: GUESSINGROWS ( EXCEL to SAS) ??

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.

Frequent Contributor
Posts: 88

Re: GUESSINGROWS ( EXCEL to SAS) ??

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. 

 

 

Contributor
Posts: 22

Re: GUESSINGROWS ( EXCEL to SAS) ??

[ Edited ]

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.

 

Frequent Contributor
Posts: 88

Re: GUESSINGROWS ( EXCEL to SAS) ??

[ Edited ]

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;
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 24851 views
  • 5 likes
  • 5 in conversation