I have the following dataset; may I know how to get the desired output? Anyone can help? Thanks.
(rate to multiply for similar case no)
data test;
input case $ rate;
datalines;
110 0.5
110 0.4
111 0.8
112 0.6
112 1.2
;
run;
Desired output
Case Rate
110 0.2
111 0.8
112 0.72
What is the logic here? How is rate calculated?
110 0.5
110 0.4
111 0.8
112 0.6
112 1.2
;
run;
Desired output
Case Rate logics
110 0.2 0.5 x 0.4 (2 cases for 110)
111 0.8 0.8 (one case only for 111)
112 0.72 0.6 x 1.2 (2 cases for 112)
Ah ok. You can do something like this. I changed case to _case, since it is a reserved keyword in PROC SQL 🙂
data test;
input _case $ rate;
datalines;
110 0.5
110 0.4
111 0.8
112 0.6
112 1.2
;
run;
proc sql;
create table want as
select _case,
exp(sum(log(rate))) as newrate
from test
group by _case;
quit;
Thank you very much.
May I know how is the following work, especially the exp? I tried to split to bit by bit; but confused on the exp part.
exp(sum(log(rate)))
This is just a workaround to simulate a 'product function'.
@scb wrote:
Thank you very much.
May I know how is the following work, especially the exp? I tried to split to bit by bit; but confused on the exp part.
exp(sum(log(rate)))
exp is the reverse of log.
From inside out:
Since summing of logarithms is equivalent to multiplying, you get a cumulative multiplication formula.
data test;
input case $ rate;
datalines;
110 0.5
110 0.4
111 0.8
112 0.6
112 1.2
;
run;
data want;
set test (rename=(rate=_rate));
by case;
retain rate;
if first.case then rate = 1;
rate = rate * _rate;
if last.case then output;
drop _rate;
run;
data test;
input case $ rate;
datalines;
110 0.5
110 0.4
111 0.8
112 0.6
112 1.2
;
run;
data want;
set test;
by case;
retain want;
if first.case then want=1;
want=want*rate;
if last.case;
drop rate;
run;
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.