topic Index applied to previous row value in SAS Programming

Index applied to previous row value

Bernardita — Fri, 13 May 2016 07:49:00 GMT

Hello Community,

I have been trying for ages to set up a small code to do the following:

ID	MyValue	Rate_1	Rate_2	Result_1	Want_1	Want_2
A	203,000	0.84	0.989	0.83076	0.8308	168,644
A	203,000	1	0.989	0.989	0.8216	166,789
A	203,000	1	0.989	0.989	0.8126	164,955
A	203,000	1	0.989	0.989	0.8036	163,140
B	203,000	0.82	0.9	0.738	0.5931	120,397
B	203,000	1	0.9	0.9	0.5338	108,358
B	203,000	1	0.9	0.9	0.4804	97,522
B	203,000	1	0.9	0.9	0.4324	87,770
C	203,000	0.77	0.989	0.76153	0.3293	66,839
C	203,000	1	0.989	0.989	0.3256	66,104
C	203,000	1	0.989	0.989	0.3221	65,377
C	203,000	1	0.989	0.989	0.3185	64,658

My big file has values like in Column ID, MyValue, Rate_1, Rate_2.

I need to obtain the colums Want_1 & Want_2; which is:

Result_1 = Rate_1 * Rate_2 (easy to do),

(Want_1,Row_1) = (Result_1,Row_1)

(Want_1,Row_2) =(Want_1,Row_1) * (Result_1,Row_2)

(Want_1,Row_3) =(Want_1,Row_2) * (Result_1,Row_3) and so on...

Indeed is the Calculation of Want_1, which is a consecutive multiplaction of current row of Result_1 with previous row value outcome of Want_1.

Want_2 is simple the multiplication of Want_1 with MyValue, row by row.

I know it can be done totally different a code, but this is way i can interpreate to exaplina my problem.

I iwll be so greatfull if someone help me to set up a code in SAS, which I need to apply to a large set of data.

Thank you very much

Bernardita

Re: Index applied to previous row value

Reeza — Fri, 13 May 2016 08:22:50 GMT

You can use the RETAIN function to hold the value across rows.

I'm surprised ID doesn't come into play somehow.


data want;
set have;
by ID;
retain want1 1;
result1=rate1*rate2;

*if first.ID then want1=1;
want1=want1*result1;

want2=want1*myvalue;
run;

Re: Index applied to previous row value

Bernardita — Fri, 13 May 2016 09:14:28 GMT

Thank you very much for your help.

Yes its true. ID does come into play. But I wanted to make the problem simpler by puting into place the Rate_1 & Rate_2 that step before. I will ask another question later in the community regarding placing Rate_1 & Rate_2 in place. Which is at the point ID changes. Rate_1 goes at First ID and later as equal to one. Rate_2 goes at the First ID but its repeated until ID changes.

TRhank you very much Reeza for your help.

Bernardita