topic Re: Adding numbers depending on a test to a column in SAS Programming

Adding numbers depending on a test to a column

CIG21 — Mon, 12 Oct 2020 06:13:12 GMT

Hi!

I am having this sample datasets which contains values and their units (K stands for ten thousands, M for millions). My goal is to have a new table (want) that adds the zeros, depending on K or M, to the value column variables and shows the value as proper number. I tried it with the below code but it did not work (did not get an error either but it didn't change anything).

In the end, the table want should ideally look like this (tips on how to remove the redudandant unit columns are also appreciated):

"value

10000

15000

19000000

4000000"

data example;
input value unit$;
datalines;
10 K
15 K
19 M
4 M
;
run;

data want;
set example;
if unit ="K" then value=value+000;
else value=value+000000;
run;

Any ideas on how I could achieve that? Thanks already in advance! 🙂

Re: Adding numbers depending on a test to a column

yabwon — Mon, 12 Oct 2020 06:58:19 GMT

As far as I know "adding zeros" means multiplying by...

data want(drop=unit);
 set example;
 if unit ="K" then value=value*1000;
                   else value=value*1000000;
run;

Bart

Re: Adding numbers depending on a test to a column

Kurt_Bremser — Mon, 12 Oct 2020 07:13:23 GMT

With numeric data, 000 is equal to 0, so your addition statements do in effect nothing. You need to multiply.

If k stands fo decimal "kilo", multiply by 1000, but if it stands for IT K (note the capital letter), multiply by 1024. Similarly, m should be multiplied by 1000000, but M by 1024*1024.

Re: Adding numbers depending on a test to a column

CIG21 — Mon, 12 Oct 2020 09:05:08 GMT

Awesome, that's exactly what I needed! Thanks so much.