topic Re: SAS query in SAS Programming

SAS query

scb — Thu, 27 Jun 2019 07:07:21 GMT

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

Re: SAS query

PeterClemmensen — Thu, 27 Jun 2019 07:08:57 GMT

What is the logic here? How is rate calculated?

Re: SAS query

scb — Thu, 27 Jun 2019 07:11:42 GMT

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)

Re: SAS query

PeterClemmensen — Thu, 27 Jun 2019 07:19:24 GMT

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;

Re: SAS query

scb — Thu, 27 Jun 2019 08:40:24 GMT

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

Re: SAS query

Kurt_Bremser — Thu, 27 Jun 2019 08:43:39 GMT

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;

Re: SAS query

PeterClemmensen — Thu, 27 Jun 2019 08:48:32 GMT

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

Re: SAS query

Kurt_Bremser — Thu, 27 Jun 2019 09:03:54 GMT

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

get logarithm of individual rate
sum up all logarithms within a group
exponentiate the result

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

Re: SAS query

Ksharp — Thu, 27 Jun 2019 12:13:10 GMT

 

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;