topic Re: GUESSINGROWS for Excel in New SAS User

GUESSINGROWS for Excel

raivester — Wed, 24 Mar 2021 19:03:29 GMT

I am importing an Excel file to SAS; however, one of variables (often 300+ characters) is getting truncated. I want to use GUESSINGROWS, but I see that it is not compatible with Excel, only text delimited data. It is the nature of the project I am using this data for to not alter the raw data we receive--i.e., I cannot convert the file itself to a compatible format. Does anyone know how I can read in the data so this variable will not be truncated?

proc import
	datafile = "&maindir.\Data\State Submissions\&year.\Raw\&state.\&stinptE."
	out = part_e_imp replace
	dbms = excel;
	GUESSINGROWS = 3000;
run;

Re: GUESSINGROWS for Excel

SASKiwi — Wed, 24 Mar 2021 19:21:11 GMT

Do these help?

https://support.sas.com/kb/17/123.html

https://support.sas.com/kb/46/472.html

Does DBMS = XLSX help?

Re: GUESSINGROWS for Excel

Reeza — Wed, 24 Mar 2021 21:35:58 GMT

Search for DBSASTYPE and using and ODBC connection instead.

Re: GUESSINGROWS for Excel

r_behata — Thu, 25 Mar 2021 00:03:16 GMT

Have you tried :

GUESSINGROWS = MAX;

Re: GUESSINGROWS for Excel

ballardw — Thu, 25 Mar 2021 01:25:08 GMT

@r_behata wrote:
Have you tried :

GUESSINGROWS = MAX;

Guessingrows is not a valid option for XLSX or XLS file formats. It is only valid for delimited text files.