SAS query

[scb]

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

[PeterClemmensen]

What is the logic here? How is rate calculated?

[scb]

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)

[PeterClemmensen]

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;

[scb]

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)))

[PeterClemmensen]

This is just a workaround to simulate a 'product function'. 

[Kurt_Bremser]

---

@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:

1. get logarithm of individual rate
2. sum up all logarithms within a group
3. exponentiate the result

Since summing of logarithms is equivalent to multiplying, you get a cumulative multiplication formula.

[Kurt_Bremser]

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;

[Ksharp]

 

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;