Help using Base SAS procedures

Repeat a value from one row to observations below

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

Repeat a value from one row to observations below

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

 

 


Accepted Solutions
Solution
‎05-31-2017 08:18 AM
Super User
Posts: 5,516

Re: Repeat a value from one row to observations below

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;

View solution in original post


All Replies
New Contributor
Posts: 4

Re: Repeat a value from one row to observations below

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.

Solution
‎05-31-2017 08:18 AM
Super User
Posts: 5,516

Re: Repeat a value from one row to observations below

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;

New Contributor
Posts: 4

Re: Repeat a value from one row to observations below

Posted in reply to Astounding

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

 

 

New Contributor
Posts: 4

Re: Repeat a value from one row to observations below

Ok, I got it to work now.

 

Thank you so much, you saved my life. <3

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 164 views
  • 1 like
  • 2 in conversation