topic convert all numeric values for all observations into the same units in SAS Programming

convert all numeric values for all observations into the same units

Satori — Thu, 02 Feb 2023 22:50:51 GMT

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

Re: convert all numeric values for all observations into the same units

SASKiwi — Fri, 03 Feb 2023 00:29:43 GMT

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;

Re: convert all numeric values for all observations into the same units

Kurt_Bremser — Fri, 03 Feb 2023 07:00:50 GMT

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;

Re: convert all numeric values for all observations into the same units

Satori — Fri, 03 Feb 2023 09:21:24 GMT

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;

Re: convert all numeric values for all observations into the same units

Kurt_Bremser — Fri, 03 Feb 2023 09:49:49 GMT

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;