Import Proc: Scanning DataTypes

[BruceTao]

Hi Community,
The following code produces almost what I need. It sets all the data types to the right value except one.
The one it does not set correctly is the result that it did not do a large enough scan on the data
to determine that it was numeric rather than character. Is there a option that I can add which will 
scan more values than the default? I know there are a number of  options starting with "SCAN" like 
"SCANTEXT" but there don't seem to fit the bill. Any insight?

PROC IMPORT OUT = Export.TDSR_InterfaceTestOutDec19_5
       DATAFILE= '/sasshare/prd/AUDIT/BlackMESA/Dec_Test4'
       DBMS=DLM REPLACE;
       DELIMITER = "|";
       GETNAMES = YES;
RUN;

[ballardw · Accepted solution]

---

@BruceTao wrote:

Hi Community,
The following code produces almost what I need. It sets all the data types to the right value except one.
The one it does not set correctly is the result that it did not do a large enough scan on the data
to determine that it was numeric rather than character. Is there a option that I can add which will 
scan more values than the default? I know there are a number of  options starting with "SCAN" like 
"SCANTEXT" but there don't seem to fit the bill. Any insight?

PROC IMPORT OUT = Export.TDSR_InterfaceTestOutDec19_5
       DATAFILE= '/sasshare/prd/AUDIT/BlackMESA/Dec_Test4'
       DBMS=DLM REPLACE;
       DELIMITER = "|";
       GETNAMES = YES;
RUN;

---

See if adding the GUESSINGROWS=  option helps. Use a number that is about the number of rows in the data file.

However if the values do not contain any characters other than digits and decimal points the value will continue to be numeric.

Another option when this occurs is the copy the data step code generated by Proc Import into the editor and modify the informat statement to use a character informat instead of numeric.

If you are going to read multiple files in the same layout you will likely be better off to keep the data step generated, make sure any lengths are long enough for expected values and the use the data step code changing the infile and the output data set. With separate guesses the lengths of variables will likely vary from file to file using import and frequently causes issues when appending multiple files together as values may get truncated. In some cases you may even have variables change types.

[BruceTao]

This worked like a charm. I really like this SAS Forum. Great and quick answers. I am glad to be apart of it.

[Astounding]

Your diagnosis isn't quite right.

If importing made a variable character, it means one or more of the values found contains characters that can't be translated to numeric. Examining additional rows won't change that. Inspect the data to see which values can't be stored as numeric, so you can decide what the proper course of action would be.