Retain and lag by group

[bebess]

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)

[Kurt_Bremser]

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

[bebess]

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

[bebess · Accepted solution]

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;

[Kurt_Bremser]

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.

[mkeintz]

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.

[bebess]

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