convert all numeric values for all observations into the same units

[Satori]

This is a sample of my data. I have about 50 numeric variables such as turnover, sales, etc.

Obs  ID_number        code   units   currency   value       Turnover        Sales          goods_sold 

1       AE00006541      U1     units     USD       50000    548741902    157000000   67000200

2       AE00037163      U2     units     USD       12000    45000000      28359000     12000322 
3       AE00037226      C1     thous    USD       60          92322            12450           7200
4       AE00037282      U2     thous    USD       6            73000            15720           9543

5       AE00043424      C2     milli      USD       1             46                  15                 9

6       AE00098593      U2     billi       USD       0.5           3                    4                  1

I want to convert all the observations into the same units (there is a variable units that takes the values "units", "thous", "milli", and "billi"). To do this, I need to take the observations with the units equal to "thous", "milli", and "billi" and divide all the numeric variables by 1000, 1000000, and 1000000000, respectively.

How can I do this?

What I would like to have in the end is:

Obs  ID_number        code  units   currency   value       Turnover        Sales            goods_sold 

1       AE00006541      U1    units   USD       50000        548741902    157000000    67000200

2       AE00037163      U2    units   USD       12000        45000000       28359000     12000322 
3       AE00037226      C1    units   USD       60000         92322000      12450000      7200000
4       AE00037282      U2    units   USD       6000           73000000      15720000      9543000

5       AE00043424      C2    units   USD       1000000     46000000      15000000      9000000

6       AE00098593      U2    units   USD       500000000 3000000000  4000000000  1000000000

[SASKiwi · Accepted solution]

Something like this should do it (untested):

data want;
  set Have;
  array values (*) value Turnover Sales goods_sold;
  do i = 1 to dim(values);
    if units = 'thous' then values(i) = 1000 * values(i);
    else if units = 'milli' then values(i) = 1000000 * values(i);
    else if units = 'billi' then values(i) = 1000000000 * values(i);
  end;
run;

[Satori]

in the line below, how can I invoke many variables (more than 50)? Do I have to name them all?

array values (*) value Turnover Sales goods_sold;

[Kurt_Bremser]

If all your numeric variables need to be converted, then use the _NUMERIC_ keyword:

array values {*} _numeric_;

If you need to make exceptions, derive the list of variables from DICTIONARY.COLUMNS with SELECT INTO:

proc sql noprint;
select name into :varnames separated by " "
from dictionary.columns
where libname = "WORK" and memname = "HAVE" /* uppercase here */
and type = "num"
and upcase(name) not in ("VAR1","VAR2");
quit;

[Kurt_Bremser]

I would create a custom informat which converts the unit string to a multiplicator, and use that in a single calculation.

proc format.
invalue mult
  "units" = 1
  "thous" = 1000
  "milli" = 1000000
  "billi" = 1000000000
;
run;

data want;
set have;
mult = input(units,mult.);
units = "units";
value = value * mult;
/* and so on, you could use an array for this */
drop mult;
run;