topic Converting currency to num displayed as a char in SAS Programming

Converting currency to num displayed as a char

Q1983 — Fri, 26 Oct 2018 21:21:23 GMT

data have;

input Group $ Value $

datalines;

BBBB $250

CCCC $500

;

run

I did the following;

data want;

set have;

val_1 = input(Value,8.); Also tried other numeric such as 12 to convert to a numeric

run;

My results do not convert to a number. Instead I get error message such as

Invalid argument to function INPUT

Is it because even though it shows as a character it includes dollar signs. How can I get this to convert to a numeric.

Re: Converting currency to num displayed as a char

novinosrin — Fri, 26 Oct 2018 21:24:34 GMT


data have;

input Group $  Value $;

datalines;
BBBB $250
CCCC $500
;

run;

data want;
set have;
val_1 = input(Value,dollar8.);
run;

Re: Converting currency to num displayed as a char

novinosrin — Fri, 26 Oct 2018 21:26:51 GMT

data want1;
set have;
val_1 = input(Value,comma10.);
run;

Re: Converting currency to num displayed as a char

Jagadishkatam — Sat, 27 Oct 2018 10:13:34 GMT

Alternatively you may try below code

data have;
input Group $  Value $;
datalines;
BBBB $250
CCCC $500
;
run;

data want;
set have;
val_1 = input(compress(Value,,'kd'),best.);
run;

Re: Converting currency to num displayed as a char

Allaluiah — Sat, 27 Oct 2018 14:45:03 GMT

@Q1983 Thank you for posting the $ currency question as I ran into this need just a few moments ago.

@Jagadishkatam Your alternative though looks nice by stripping chars and keeping digits, but is far slower than the straight forward offered one by @novinosrin

I just tested with about 20 million records and your solution exponentially gets slower as I increase the test with adding number of records. Could you offer something that is rather better to beat that ?

Re: Converting currency to num displayed as a char

novinosrin — Sat, 27 Oct 2018 14:58:53 GMT

@Allaluiah Oh well, it's interesting that you tested the solutions offered here and I appreciate the feedback. I didn't quite expect the diligence by folks on this community for simple solutions.

Ok here you go, this is another one which beat the two variants I posted. So I beat myself . lol


data want;
set have;
val_1 = input(substr(value,2),8.);
run;

Re: Converting currency to num displayed as a char

novinosrin — Sat, 27 Oct 2018 15:02:49 GMT

And here is my Log report of my test with 30 Million observations

932 /*Compress char and keep digit*/
933 data want;
934 set have;
935 val_1 = input(compress(Value,,'kd'),best.);
936 run;

NOTE: There were 30000000 observations read from the data set WORK.HAVE.
NOTE: The data set WORK.WANT has 30000000 observations and 3 variables.
NOTE: DATA statement used (Total process time):
real time 6.14 seconds
cpu time 6.14 seconds

937 /*extract num from currenct and apply informat */
938 data want3;
939 set have;
940 val_1 = input(substr(value,2),8.);
941 run;

NOTE: There were 30000000 observations read from the data set WORK.HAVE.
NOTE: The data set WORK.WANT3 has 30000000 observations and 3 variables.
NOTE: DATA statement used (Total process time):
real time 2.85 seconds
cpu time 2.73 seconds

942 /*comma informat */
943 data want1;
944 set have;
945 val_1 = input(Value,comma10.);
946 run;

NOTE: There were 30000000 observations read from the data set WORK.HAVE.
NOTE: The data set WORK.WANT1 has 30000000 observations and 3 variables.
NOTE: DATA statement used (Total process time):
real time 3.67 seconds
cpu time 3.57 seconds