Can we add the length statement in PROC IMPORT

[souji]

Proc import reading the delimited text file:

which has 100 rows and 10 variables

variable name First_Name

first 20 rows, the longest value of First_Name is 10 characters

In the delimited file, later rows has longest value of First_Name is 30 characters

Should I use length statement or guessingrows= option will work

I could not find in sas prep guide

thank you

[Tom · Accepted solution]

The GUESSINGROWS option will work for that single file.

But if you want to get consistent results across multiple potential versions of the same data then create a data step to read the delimited file directly instead of forcing SAS to guess how to define the variables based on the examples in the one version of the file it is currently looking at.

[Editors Note - Start]
As per the PROC IMPORT documentation.
The procedure scans the first 20 rows to count the variables, and it attempts to determine the correct informat and format for each variable.

You can adjust the number of rows scanned with the GUESSINGROWS Statement

By using PROC IMPORT you are letting the code determine the format of your data, and that can result in issues. For example say you have a column that contains 12032023. Is this the number 12,032,023, a date in December 12/03/2023, (MM/DD/YYYY), a date in March 12/03/2023 (DD/MM/YYYY), or something else?

As mentioned later in this post, you probably will want to take ownership of your code and control how to read the data, which you can do with the INPUT Statement.
[Editors Note - End]

[souji]

If I use GUESIINGROES=yes;

Then, it will read all value (30 character ) instead of up to 10 characters  for remaining variable right

[souji]

Correcting**

GUESSINGROWS=yes;

[Tom]

What does the documentation tell you?

https://documentation.sas.com/?docsetId=proc&docsetTarget=p13kvtl8ezj13in17i6m99jypcwi.htm&docsetVer...

[ed_sas_member]

Hi @souji ,

you can specify a specific number to indicates the number of rows to scan in the input file to determine the appropriate data type and length of variables:

	guessingrows=30;

or you can simply put 'max', but it could affect performance, depending on the size of your dataset.

	guessingrows=max;

[Kurt_Bremser]

To take control of the import process, take the guessing of proc import out of it. Copy the data step (that proc import created) from the log, and adapt it to your column lengths as documented for the delimited file. By studying this data step (and making use of the documentation), you will be able to do a lot of optimization by omitting unnecessary statements or part of statements.