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.
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.
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??
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.
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.
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.
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;
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.
Ready to level-up your skills? Choose your own adventure.