subtracting number by previous value by group

pacman94 — Fri, 29 Jan 2021 18:13:39 GMT

I have the following table. They are sorted by Group and Year.

What I would like to do is that: for each group, I would like to subtract the previous value from the previous row but keep the first observation value from each group as the same first value.

Group	Year	Rate
A	2019	9.090909
A	2020	27.27273
A	2021	31.81818
B	2018	22.72727
B	2019	72.72727
B	2020	95.45455
B	2021	100
C	2018	31.81818
C	2019	54.54545
C	2020	68.18182
C	2021	68.18182

Want:

Group	Year	Rate	rate_2
A	2019	9.090909	9.090909
A	2020	27.27273	18.181821
A	2021	31.81818	4.54545
B	2018	22.72727	22.72727
B	2019	72.72727	50
B	2020	95.45455	22.72728
B	2021	100	4.54545
C	2018	31.81818	31.81818
C	2019	54.54545	22.72727
C	2020	68.18182	13.63637
C	2021	68.18182	0

I tried the following

data want; set test;

by group;

if first.group then rate_2 = rate;

else if rate_2 ..?

run;

Re: subtracting number by previous value by group

SASJedi — Mon, 01 Feb 2021 19:06:44 GMT

The LAG function is you friend here, but make sure you always execute the LAG - never put a call to the LAG function in a conditional code block. Try this:

data have;
  infile datalines truncover;
  input Group:$1. Year:4. Rate:10.;
datalines;
A	2019	9.090909
A	2020	27.27273
A	2021	31.81818
B	2018	22.72727
B	2019	72.72727
B	2020	95.45455
B	2021	100
C	2018	31.81818
C	2019	54.54545
C	2020	68.18182
C	2021	68.18182
;

data want;
   set have;
   by group;
   /* ALWAYS execute the LAG function - never make it conditional */
   rate_2=sum(Rate,-lag1(rate));
   if first.group then rate_2=rate;
run;

proc print;
run;

topic subtracting number by previous value by group in SAS Programming

subtracting number by previous value by group

Re: subtracting number by previous value by group