Dear SAS users,
I have a data set similar to the one below. I want to calculate "ratio" based values specified on the 'type' variable without hard coding the type values. since the type could be different month to month.
For example:
for row 1:
ratio = (_1a_val * 100)/360;
row 3:
ratio = (_1b_val * 100)/360;
data have;
input class $ type $ month _1a_val _1b_val _1c_val;
datalines;
Plas-1 1a1 0 2.5 3.5 4.5
Plas-1 1a1 1 2.5 3.5 4.5
Plas-1 1b1 0 2.5 3.5 4.5
Plas-1 1b1 1 2.5 3.5 4.5
Plas-1 1c1 0 2.5 3.5 4.5
Plas-1 1c1 1 2.5 3.5 4.5
Plas-1 1c1 2 2.5 3.5 4.5
blod-2 1a1 0 1.5 2.5 3.5
blod-2 1a1 1 1.5 2.5 3.5
blod-2 1b1 0 1.5 2.5 3.5
blod-2 1b1 1 1.5 2.5 3.5
blod-2 1b1 2 1.5 2.5 3.5
blod-2 1c1 0 1.5 2.5 3.5
blod-2 1c1 1 1.5 2.5 3.5
blod-2 1c1 2 1.5 2.5 3.5
;
run;
Here is the result. I want
class type month _1a_val _1b_val _1c_val ratio
Plas-1 1a1 0 2.5 3.5 4.5 0.6944444444
Plas-1 1a1 1 2.5 3.5 4.5 0.6944444444
Plas-1 1b1 0 2.5 3.5 4.5 0.9722222222
Plas-1 1b1 1 2.5 3.5 4.5 0.9722222222
Plas-1 1c1 0 2.5 3.5 4.5 1.25
Plas-1 1c1 1 2.5 3.5 4.5 1.25
Plas-1 1c1 2 2.5 3.5 4.5 1.25
blod-2 1a1 0 1.5 2.5 3.5 0.4166666667
blod-2 1a1 1 1.5 2.5 3.5 0.4166666667
blod-2 1b1 0 1.5 2.5 3.5 0.6944444444
blod-2 1b1 1 1.5 2.5 3.5 0.6944444444
blod-2 1b1 2 1.5 2.5 3.5 0.6944444444
blod-2 1c1 0 1.5 2.5 3.5 0.9722222222
blod-2 1c1 1 1.5 2.5 3.5 0.9722222222
blod-2 1c1 2 1.5 2.5 3.5 0.9722222222
data have;
input class $ type $ month _1a_val _1b_val _1c_val;
datalines;
Plas-1 1a1 0 2.5 3.5 4.5
Plas-1 1a1 1 2.5 3.5 4.5
Plas-1 1b1 0 2.5 3.5 4.5
Plas-1 1b1 1 2.5 3.5 4.5
Plas-1 1c1 0 2.5 3.5 4.5
Plas-1 1c1 1 2.5 3.5 4.5
Plas-1 1c1 2 2.5 3.5 4.5
blod-2 1a1 0 1.5 2.5 3.5
blod-2 1a1 1 1.5 2.5 3.5
blod-2 1b1 0 1.5 2.5 3.5
blod-2 1b1 1 1.5 2.5 3.5
blod-2 1b1 2 1.5 2.5 3.5
blod-2 1c1 0 1.5 2.5 3.5
blod-2 1c1 1 1.5 2.5 3.5
blod-2 1c1 2 1.5 2.5 3.5
;
run;
data want;
array ratio_arr{*} _1a_val _1b_val _1c_val;
i+1;
do until(last.type);
set have;
by class type;
if first.class then i=1;
end;
do until(last.type);
set have;
by class type;
ratio =(ratio_arr[i]*100)/360;
output;
end;
drop i;
run;
Are you saying that the names of the variables can change over time?
If not then just code an IF statement.
if type='1a1' then ratio = (_1a_val * 100)/360;
else if type='1b1' then ratio = (_1b_val * 100)/360;
else if type='1c1' then ratio = (_1c_val * 100)/360;
If you can change how TYPE is coded then it would be even easier.
If you make TYPE an integer from 1 to 3 then you could just use an array.
array value _1a_val _1b_val _1c_val ;
ratio = (value[type] * 100)/360;
data have;
input class $ type $ month _1a_val _1b_val _1c_val;
datalines;
Plas-1 1a1 0 2.5 3.5 4.5
Plas-1 1a1 1 2.5 3.5 4.5
Plas-1 1b1 0 2.5 3.5 4.5
Plas-1 1b1 1 2.5 3.5 4.5
Plas-1 1c1 0 2.5 3.5 4.5
Plas-1 1c1 1 2.5 3.5 4.5
Plas-1 1c1 2 2.5 3.5 4.5
blod-2 1a1 0 1.5 2.5 3.5
blod-2 1a1 1 1.5 2.5 3.5
blod-2 1b1 0 1.5 2.5 3.5
blod-2 1b1 1 1.5 2.5 3.5
blod-2 1b1 2 1.5 2.5 3.5
blod-2 1c1 0 1.5 2.5 3.5
blod-2 1c1 1 1.5 2.5 3.5
blod-2 1c1 2 1.5 2.5 3.5
;
run;
data want;
array ratio_arr{*} _1a_val _1b_val _1c_val;
i+1;
do until(last.type);
set have;
by class type;
if first.class then i=1;
end;
do until(last.type);
set have;
by class type;
ratio =(ratio_arr[i]*100)/360;
output;
end;
drop i;
run;
Thanks for your help. this solution works.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.