Help using Base SAS procedures

proc import question

Reply
N/A
Posts: 0

proc import question

I am having a problem importing a list of customer ids correctly using proc import. The customer id is made up a 20 digit number which should be treated as a string. when I use proc import the id gets converted into a number and written in shortened format (e.g. 2.001365E18 ). When I try to run a format "best32." on the field, I get the length of the id back, but most of the ending digits are wrong. Can someone help me? There must be a way to make proc import treat this field correctly.

Here is my original data file:
CUST_ID|ST_CD|age
2001365500203000150|SC|55.14
2001365600005000294|FL|68.44
2001365600006003375|FL|63.22
2001365600006003464|FL|68.33

here is my import/format statement:
PROC import out = pne_db
datafile = "C:\Temp\test_forum.txt"
DBMS = DLM REPLACE;
DELIMITER='|';
GETNAMES=yes;
run;

data pne_db;
set pne_db;
format cust_id best32.;
run;

Here is my result:
CUST_ID ST_CD age
2001365500203000064 SC 55.14
2001365600005000192 FL 68.44
2001365600006003456 FL 63.22
2001365600006003456 FL 68.33

Thanks in advance!
Super User
Posts: 5,441

Re: proc import question

Posted in reply to deleted_user
I think that run into problems with the largest integer (you might have more digits than the limit for your operating system). For Windows, see http://support.sas.com/documentation/cdl/en/hostwin/59544/HTML/default/numvar.htm.

I don't know any way to force proc import to treat your numeric column as character, so you might have to use a traditional data step to read your data.

Regards,
Linus
Data never sleeps
Super Contributor
Super Contributor
Posts: 3,174

Re: proc import question

If you are destined to use PROC IMPORT rather than coding your own DATA step, one technique that may work, given your example input file, would be to append a unique prefix character to the CUST_ID field and then strip off the character after the data is read up. So, if you do not have control of the layout of your raw, input data creating, then you would need to code a DATA step such as:

DATA _NULL_;
INFILE INDATA;
INPUT;
PUT @1 'Z' _INFILE_;
RUN;

Then, after the PROC IMPORT, you would need to code some DATA step logic to strip off the leading "Z" character with something like:

CUST_ID = SUBSTR(CUST_ID,2);

I typically use this technique when generating Excel data with SAS, as CSV-formatted, delimited files, adding a single-quote character prefix, so Excel will treat the data as text during wizard-import without my telling it to do so.

Scott Barry
SBBWorks, Inc.
Ask a Question
Discussion stats
  • 2 replies
  • 143 views
  • 0 likes
  • 3 in conversation