SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Converting long characters into numerics with decimals

Accepted Solution Solved
Reply
Occasional Contributor ENM
Occasional Contributor
Posts: 12
Accepted Solution

Converting long characters into numerics with decimals

Hello,

I have trouble converting particularly long character values such as "20111019111006.18993" correctly into numeric with up to five decimals. The latest I've tried is "input(ct_CLAIM_TRANS_ID, d19.5)", with informat 19.5 and format 19.5. Here's what that ends up looking like. I want the right-hand column values to be equal to the left-hand column values.

Skjermbilde.PNG

Can anyone please offer some advice on this? Thanks.


Accepted Solutions
Solution
‎03-19-2014 01:19 PM
Super User
Posts: 11,343

Re: Converting long characters into numerics with decimals

Have you tried the SORTSEQ option in proc sort using NUMERIC_COLLATION=ON? It might avoid the whole issue. It seems to work with this example:

 

data junk;

x = '111111111111111111111.2222222222';output;

x = '2222';output;

run;

proc sort sortseq=linguistic (NUMERIC_COLLATION=on);by x;run;

View solution in original post


All Replies
Super User
Posts: 5,433

Re: Converting long characters into numerics with decimals

There are several discussions on the forum regarding long numerical data.

Since you are not going to make any calculations (it's an id, right?), why can't you keep it as char?

Data never sleeps
Occasional Contributor ENM
Occasional Contributor
Posts: 12

Re: Converting long characters into numerics with decimals

Ok I see, thanks. Each row represents a payment, and is associated with a policy ID. There's a field which calculates how much is paid so far (it takes the payment amount from the previous row, and adds the payment amount from the current row), per policy ID. In order for this amount to be set in the correct order per policy, the data must be sorted based on transaction number. If the transaction number is character, then the number 94 would come after 124578. Making the transaction ID numeric is only needed for the UWC which is used to calculate how much is paid so far; it remains a character for all other purposes.

PROC Star
Posts: 1,167

Re: Converting long characters into numerics with decimals

Another option is to select a length, that will be longer than all of your "integer" parts of the transaction number, and then pad them all to the left with leading zeroes. This will avoid the risks associated with converting it to numeric, and the sort will work correctly. So the IDs would become:

00000000842825

00000000228390.2086

20111212111209.5639

Tom

Super User
Super User
Posts: 7,066

Re: Converting long characters into numerics with decimals

Only if you left align the digits in the character variable.  Pad them on the left with zeros or spaces.

Solution
‎03-19-2014 01:19 PM
Super User
Posts: 11,343

Re: Converting long characters into numerics with decimals

Have you tried the SORTSEQ option in proc sort using NUMERIC_COLLATION=ON? It might avoid the whole issue. It seems to work with this example:

 

data junk;

x = '111111111111111111111.2222222222';output;

x = '2222';output;

run;

proc sort sortseq=linguistic (NUMERIC_COLLATION=on);by x;run;

PROC Star
Posts: 1,167

Re: Converting long characters into numerics with decimals

Very nice! I hadn't seen this one before!!

Tom

Super User
Super User
Posts: 7,977

Re: Converting long characters into numerics with decimals

If you need to sort numerically and that's all, could you do it with two variables, one whole, one part and then use both in the sort:

data have;

  attrib ct_claim_trans_id format=$200.;

  ct_claim_trans_id="842825";output;

  ct_claim_trans_id="228390.2086";output;

  ct_claim_trans_id="20111212111209.5639";output;

run;

data want;

  set have;

  attrib ct_claim_trans_no format=best19.5 inter format=best19.;

  if index(ct_claim_trans_id,".")>0 then do;

    ct_claim_trans_no=input(substr(ct_claim_trans_id,1,index(ct_claim_trans_id,".")-1),best19.);

    inter=input(substr(ct_claim_trans_id,index(ct_claim_trans_id,".")+1),best19.)/10000;

  end;

  else do;

    ct_claim_trans_no=input(strip(ct_claim_trans_id),best.);

  end;

run;

proc sort...

by ct_claim_trans_no inter;

run;

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 526 views
  • 6 likes
  • 6 in conversation