BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Satori
Quartz | Level 8

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

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
SASKiwi
PROC Star

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;

View solution in original post

4 REPLIES 4
SASKiwi
PROC Star

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
Quartz | Level 8

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
Super User

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
Super User

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;

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 639 views
  • 1 like
  • 3 in conversation