topic Re: New calculation based on previous row calculation in SAS Programming

New calculation based on previous row calculation

PetePatel — Thu, 05 Mar 2020 15:42:57 GMT

I have a dataset with columns Month, A and Q as below:

Month	A	Q	Calc
Jan-19	10.00%
Feb-19	20.00%	21.00%	12.10%
Mar-19	15.00%	16.00%	14.04%
Apr-19	30.00%	29.00%	18.11%
May-19	40.00%	39.00%	25.17%
Jun-19	30.00%	29.00%	32.47%
Jul-19	10.00%	11.00%	36.04%

I want to create Calc, where first answer is 10%*(1+21%)=12.10%
The next row onwards uses the previous rows calc so 12.10%*(1+16%)=14.04%

What is the best way of doing this in SAS so it doesnt have to be done in Excel?

Thanks!

Re: New calculation based on previous row calculation

PaigeMiller — Thu, 05 Mar 2020 15:54:35 GMT

Are the values of A and Q on row two 0.20 and 0.21 respectively, or are they character strings with a % sign at the end?

Re: New calculation based on previous row calculation

PetePatel — Thu, 05 Mar 2020 15:57:22 GMT

Hi Paige, they are numeric so 0.20 and 0.21

Re: New calculation based on previous row calculation

novinosrin — Thu, 05 Mar 2020 16:06:00 GMT

data have;
input Month	: monyy7. A	: percent2. Q  :percent2.;*	Calc;
format month monyy7. a q percent.;
cards;
Jan-19	10.00%	 	.   . 
Feb-19	20.00%	21.00%	12.10%
Mar-19	15.00%	16.00%	14.04%
Apr-19	30.00%	29.00%	18.11%
May-19	40.00%	39.00%	25.17%
Jun-19	30.00%	29.00%	32.47%
Jul-19	10.00%	11.00%	36.04%
;
/*10%*(1+21%)=12.10%*/
data want;
 set have;
 retain calc ;
 if _n_=1 then calc=a;
 else   calc=calc*(1+q);
 format calc percent8.2;
run;

Re: New calculation based on previous row calculation

novinosrin — Thu, 05 Mar 2020 16:13:12 GMT

More precise


data have;
input Month	: monyy7. A	: percent2. Q  :percent2.;*	Calc;
format month monyy7. a q percent.;
cards;
Jan-19	10.00%	 	.   . 
Feb-19	20.00%	21.00%	12.10%
Mar-19	15.00%	16.00%	14.04%
Apr-19	30.00%	29.00%	18.11%
May-19	40.00%	39.00%	25.17%
Jun-19	30.00%	29.00%	32.47%
Jul-19	10.00%	11.00%	36.04%
;
data want;
 set have;
 retain  calc;
 if _n_=1 then do;
   output;
   calc=a;
 end;
 else do; 
   calc=calc*(1+q);
   output;
 end;
 format calc percent8.2;
run;

Re: New calculation based on previous row calculation

PetePatel — Thu, 05 Mar 2020 16:38:23 GMT

Hi, this doesn't appear to run correctly. Calc is the newly created calculation so do you just need a and q in the dataset?

Re: New calculation based on previous row calculation

novinosrin — Thu, 05 Mar 2020 16:43:44 GMT

Hi @PetePatel Here is my understanding

1. You have a dataset with 3 variables- Month, A and Q

2. You want to create a new variable by the name CALC using a logic/formula

3. I believe the the code that I wrote does that as I tested with the sample you gave me.

Am I missing something?

Re: New calculation based on previous row calculation

Tom — Thu, 05 Mar 2020 16:44:22 GMT

@PetePatel wrote:

Hi, this doesn't appear to run correctly. Calc is the newly created calculation so do you just need a and q in the dataset?

You do not want the new "retained" variable in the incoming dataset. If it is there then when SAS reads the next observation the retained value is overwritten with the value read from the dataset.

Re: New calculation based on previous row calculation

PetePatel — Thu, 05 Mar 2020 16:47:44 GMT

Hi @novinosrin ,

When I run the code through exactly as you've provided, it gives me a dataset with month (Jan, Feb and May only) with A, Q and Calc as missing (.)

I also wondered why you specified the calc values upfront.

Re: New calculation based on previous row calculation

novinosrin — Thu, 05 Mar 2020 16:54:03 GMT

I am not sure what dataset you are running against and if you are able to follow the logic. Assuming your sample is a good representative sample, Here is my test using your sample.

data have;
input Month	: monyy7. A	: percent2. Q  :percent2.;*	Calc;
format month monyy7. a q percent.;
cards;
Jan-19	10.00%	 	.   . 
Feb-19	20.00%	21.00%	12.10%
Mar-19	15.00%	16.00%	14.04%
Apr-19	30.00%	29.00%	18.11%
May-19	40.00%	39.00%	25.17%
Jun-19	30.00%	29.00%	32.47%
Jul-19	10.00%	11.00%	36.04%
;
data want;
 set have;
 retain  calc;
 if _n_=1 then do;
   output;
   calc=a;
 end;
 else do; 
   calc=calc*(1+q);
   output;
 end;
 format calc percent8.2;
run;
proc print noobs;run;

RESULT:

Month	A	Q	calc
JAN2019	10%	.	.
FEB2019	20%	21%	12.10%
MAR2019	15%	16%	14.04%
APR2019	30%	29%	18.11%
MAY2019	40%	39%	25.17%
JUN2019	30%	29%	32.47%
JUL2019	10%	11%	36.04%

.The calc variable is the new computed variable ( requirement)

Re: New calculation based on previous row calculation

PetePatel — Thu, 05 Mar 2020 16:58:39 GMT

Thanks.

That's incredibly strange as when I run your code through without making changes I get:

Month A Q calc

FEB2019	.	.	.
MAY2019	.	.	.

I wonder if it's a SAS issue

Re: New calculation based on previous row calculation

Tom — Thu, 05 Mar 2020 17:01:22 GMT

@PetePatel wrote:

Thanks.

That's incredibly strange as when I run your code through without making changes I get:

Month A Q calc

FEB2019 . . .

MAY2019 . . .

I wonder if it's a SAS issue

Most likely a copy and paste issue. Make sure that you have to inserted TAB characters into the text.

Re: New calculation based on previous row calculation

novinosrin — Thu, 05 Mar 2020 17:05:43 GMT

Hi @PetePatel Riight, I can sense where the heck the problem is . I believe you are executing in SAS EG am i right?

Like @Tom pointed the TAB chars, add to the code and try

infile cards expandtabs;

Full version

data have;
infile cards expandtabs;
input Month	: monyy7. A	: percent2. Q  :percent2.;*	Calc;
format month monyy7. a q percent.;
cards;
Jan-19	10.00%	 	.   . 
Feb-19	20.00%	21.00%	12.10%
Mar-19	15.00%	16.00%	14.04%
Apr-19	30.00%	29.00%	18.11%
May-19	40.00%	39.00%	25.17%
Jun-19	30.00%	29.00%	32.47%
Jul-19	10.00%	11.00%	36.04%
;
data want;
 set have;
 retain  calc;
 if _n_=1 then do;
   output;
   calc=a;
 end;
 else do; 
   calc=calc*(1+q);
   output;
 end;
 format calc percent8.2;
run;

Re: New calculation based on previous row calculation

PetePatel — Thu, 05 Mar 2020 17:21:41 GMT

Yes sorry should have mentioned SAS EG.

Works perfectly now thanks!