topic Re: Converting long characters into numerics with decimals in SAS Data Management

Converting long characters into numerics with decimals

ENM — Wed, 19 Mar 2014 09:17:47 GMT

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.

Re: Converting long characters into numerics with decimals

LinusH — Wed, 19 Mar 2014 09:39:05 GMT

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?

Re: Converting long characters into numerics with decimals

ENM — Wed, 19 Mar 2014 09:59:24 GMT

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.

Re: Converting long characters into numerics with decimals

RW9 — Wed, 19 Mar 2014 10:38:24 GMT

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;

Re: Converting long characters into numerics with decimals

TomKari — Wed, 19 Mar 2014 14:18:46 GMT

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

Re: Converting long characters into numerics with decimals

Tom — Wed, 19 Mar 2014 14:34:24 GMT

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

Re: Converting long characters into numerics with decimals

ballardw — Wed, 19 Mar 2014 17:19:19 GMT

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;

Re: Converting long characters into numerics with decimals

TomKari — Wed, 19 Mar 2014 18:40:16 GMT

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

Tom