DATA Step, Macro, Functions and more

Index applied to previous row value

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 11
Accepted Solution

Index applied to previous row value

Hello Community,

 

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

 

IDMyValueRate_1Rate_2Result_1Want_1Want_2
A203,0000.840.9890.830760.8308168,644
A203,00010.9890.9890.8216166,789
A203,00010.9890.9890.8126164,955
A203,00010.9890.9890.8036163,140
B203,0000.820.90.7380.5931120,397
B203,00010.90.90.5338108,358
B203,00010.90.90.480497,522
B203,00010.90.90.432487,770
C203,0000.770.9890.761530.329366,839
C203,00010.9890.9890.325666,104
C203,00010.9890.9890.322165,377
C203,00010.9890.9890.318564,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

 

 

 


Accepted Solutions
Solution
‎05-18-2016 12:13 PM
Super User
Posts: 19,815

Re: Index applied to previous row value

Posted in reply to Bernardita

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;

View solution in original post


All Replies
Solution
‎05-18-2016 12:13 PM
Super User
Posts: 19,815

Re: Index applied to previous row value

Posted in reply to Bernardita

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;
Occasional Contributor
Posts: 11

Re: Index applied to previous row value

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

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 177 views
  • 1 like
  • 2 in conversation