BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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!
2 REPLIES 2
LinusH
Tourmaline | Level 20
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
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 735 views
  • 0 likes
  • 3 in conversation