Converting long characters into numerics with decimals

[ENM]

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.

Can anyone please offer some advice on this? Thanks.

[LinusH]

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?

[ENM]

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.

[TomKari]

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

[Tom]

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

[ballardw · Accepted solution]

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;

[TomKari]

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

Tom

[RW9]

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;