topic Re: Retain and lag by group in SAS Programming

Retain and lag by group

bebess — Mon, 07 Dec 2020 12:01:50 GMT

Hi everyone,

i try to reproduce the scenario below , any help / idea ?

YEAR	id	x	ord	WANT	EXCEL Formula
1	1	0,00218127	1	0,99781873	1-C2
2	1	0,00278531	2	0,9950395	E2*(1-C3)
3	1	0,00519557	3	0,9898697	E3*(1-C4)
3	1	0,00519557	4	0,9898697	E3*(1-C5)
4	1	0,00791179	5	0,98203806	E5*(1-C6)
4	1	0,00791179	6	0,98203806	E5*(1-C7)
1	2	0,00218127	7	0,99781873	1-C8
2	2	0,00278531	8	0,9950395	E8*(1-C9)
3	2	0,00278531	9	0,992268	E9*(1-C10)

Re: Retain and lag by group

Kurt_Bremser — Mon, 07 Dec 2020 12:11:49 GMT

data want;
set have;
by id;
retain want;
if first.id
then want = 1 - x;
else want = want * (1 - x);
run;

Re: Retain and lag by group

bebess — Mon, 07 Dec 2020 12:26:55 GMT

i think something is missing because below what i have with your code :

in my case , i would like to keep same value from each same combination of year and id.

in the excel formula i always take previsous value of combiantion of year and id ( and not the last record )

example : year=3 and id=1 i expect to have same value 0,989869698 and not 0,984726761

year	id	x	want
1	1	0,00218127	0,99781873
2	1	0,00278531	0,995039496
3	1	0,00519557	0,989869698
3	1	0,00519557	0,984726761
4	1	0,00791179	0,97693581
4	1	0,00791179	0,969206499
5	1	0,0096322	0,959870908
5	1	0,0096322	0,950625239
6	1	0,01199142	0,939225893
6	1	0,01199142	0,92796324
7	1	0,01567087	0,913421249

Re: Retain and lag by group

bebess — Mon, 07 Dec 2020 15:48:17 GMT

thanks for your help , i added a line to the following code, in that case the count of _want restart to 1 for each new id in the dataset

your remarks is welcome 🙂

data want(drop=_:);
set have;
by id year;
if first.id then want=1-x;
if first.id then _want=1; /* to restart _want equal to 1 for each new id and not keep previsou value of the previous id*/
else want=_want*(1-x);
if last.year then _want=want;
retain _want;
run;

Re: Retain and lag by group

Kurt_Bremser — Mon, 07 Dec 2020 16:10:01 GMT

Seems like you got it!

I would slightly change the layout of the code:

data want (drop=_:);
set have;
by id year;
retain _want;
if first.id
then do;
  want = 1 - x;
  _want = 1;
end;
else want = _want * (1 - x);
if last.year then _want = want;
run;

mainly to avoid the two IF FIRST.ID.

Re: Retain and lag by group

mkeintz — Mon, 07 Dec 2020 19:52:03 GMT

You can produce what you want by including SET/BY pair of statements inside a do until (last.id) loop:

data want;
  do until (last.id);
    set have;
	by id year;
	if first.year then _factor=coalesce(want,1);
	want=_factor*(1-x);
	output;
  end;
run;

Inside the loop, the _FACTORr variable is automatically retained, so it's easily updated with the advent of each new year. And that updated value is taken from WANT (also retained inside the loop) prior to the recalculation of WANT.

The coalesce function is needed to establish a _FACTOR of 1 at the start of each id, since WANT is not retained across id's.

Re: Retain and lag by group

bebess — Mon, 07 Dec 2020 21:28:08 GMT

thanks for sharing, will have a look on it !)