Repeat a value from one row to observations below

[Nici]

Hello everyone,

I have been looking through this Forum a lot and really appreciate all the solutions I was able to derive from here.

However now I'm having a problem I've been struggling with for the last two days. This is my first post about SAS so I might not be the best in explaining my problem.

So i have a dataset that is something like the following:

ID    year      EQ       flag

1      1900      5           0

1      1901      3           0

1      1902      1           0

2      2000     -1         2000

2      2001      1           0

3      1970      -1        1970

3      1971      0           0

alright so now I want to get a new coloum "Help" that copies the last flag value like the following:

ID    year      EQ       flag        HELP

1      1900      5           0             0

1      1901      3           0             0

1      1902      1           0             0

2      2000     -1         2000      2000

2      2001      1           0          2000

2      2002      1           0          2000

2      2003      1           0          2000

3      1970      -1        1970      1970

3      1971      0           0          1970

If've been trying to use the ifn function in combination with the Lag function to do this.

1)    HELP= ifn(flag, flag, LAG(Help))

I tried the following as well

2)    HELP= ifn(flag=0, LAG(Help), flag)

1) However is only copying the Help value for one following row after that it is zero again. It gives me a result like:

ID    year      EQ       flag        HELP

1      1900      5           0             0

1      1901      3           0             0

1      1902      1           0             0

2      2000     -1         2000      2000

2      2001      1           0          2000

2      2002      1           0             0

2      2003      1           0             0

3      1970      -1        1970      1970

3      1971      0           0          1970

2) Works a little better but only copies the value for the next two rows and after that gives me zero again:

ID    year      EQ       flag        HELP

1      1900      5           0             0

1      1901      3           0             0

1      1902      1           0             0

2      2000     -1         2000      2000

2      2001      1           0          2000

2      2002      1           0          2000

2      2003      1           0             0

3      1970      -1        1970      1970

3      1971      0           0          1970

I don't really understand why the ifn is stopping to execute. I hope someone can explain me why this is happening or has a solution to my problem.

Hopefully I expressed my problem precisely enough.

Thanks in advance for all tips.

Best regards

[Nici]

Sorry I just noticed I confused the solutions.

The result I posted for 1) is actually the one for 2) and the one for 2) is the one for 1). It shouldn't be much of a problem.

[Astounding · Accepted solution]

Once you see it, I suspect it makes a lot of sense:

data want;

set have;

by id;

retain help;

if first.id or flag ne 0 then help = flag;

run;

[Nici]

Thank you for your very fast reply. Your code is intuitive and I understand the logic behind it.

However if I use this code SAS basically doesn't do anything, why is that?

I receive:

ID    year      EQ       flag        HELP

1      1900      5           0             0

1      1901      3           0             0

1      1902      1           0             0

2      2000     -1         2000      2000

2      2001      1           0             0

2      2002      1           0             0

2      2003      1           0             0

3      1970      -1        1970      1970

3      1971      0           0             0

[Nici]

Ok, I got it to work now.

Thank you so much, you saved my life. ❤️