How to replace the subsequent rows with a value right above?

KS99 — Fri, 25 Feb 2022 17:35:19 GMT

Hi all,

I am really marveled by your expertise and I also sincerely appreciate your help.

I have the following dataset:

ID CUSIP YEAR Diff
3 20030N 1987 1
3 20030N 1988 1
3 20030N 1989 1
3 20030N 1996 7
3 20030N 1997 1
3 20030N 1998 1
12 08658U 1994 1
12 08658U 1995 1
12 08658U 1996 1
12 08658U 1997 1
13 001547 2007 1
13 001547 2008 1
13 001547 2011 3
13 001547 2012 1
16 101137 1996 1
16 101137 1997 1
16 101137 1998 1
16 101137 1999 1
16 101137 2007 8
16 101137 2008 1
16 101137 2009 1
16 101137 2010 1

By group ID-Cusip, the "Diff" starts with 1. Subsequent values of Diff can either be 1's or a value larger than 1. But, even if a value larger than 1 appears, Diff regains 1 after that.

What I want to create is:

By ID-Cusip group, if a value larger than 1 appears, I want to replace all the subsequent 1's with that value (larger than 1).

So, the data I want would look like this:

ID CUSIP YEAR Diff Newvar
3 20030N 1987 1 1
3 20030N 1988 1 1
3 20030N 1989 1 1
3 20030N 1996 7 7
3 20030N 1997 1 7
3 20030N 1998 1 7
12 08658U 1994 1 1
12 08658U 1995 1 1
12 08658U 1996 1 1
12 08658U 1997 1 1
13 001547 2007 1 1
13 001547 2008 1 1
13 001547 2011 3 3
13 001547 2012 1 3
16 101137 1996 1 1
16 101137 1997 1 1
16 101137 1998 1 1
16 101137 1999 1 1
16 101137 2007 8 8
16 101137 2008 1 8
16 101137 2009 1 8
16 101137 2010 1 8

Thank you in advance!!

KS -,

Re: How to replace the subsequent rows with a value right above?

PaigeMiller — Fri, 25 Feb 2022 17:40:46 GMT

data want;
    set have;
    by id cusip;
    retain newvar;
    if first.cusip then newvar=1;
    if diff>1 then newvar=diff;
run;

This is a good place to use the RETAIN statement. The variable(s) in RETAIN statements keeps the value from the previous row, unless the variable is explicity assigned a new value.

Re: How to replace the subsequent rows with a value right above?

KS99 — Fri, 25 Feb 2022 18:26:18 GMT

Thank you, Sir,
It works perfectly!

topic Re: How to replace the subsequent rows with a value right above? in SAS Programming

How to replace the subsequent rows with a value right above?

Re: How to replace the subsequent rows with a value right above?

Re: How to replace the subsequent rows with a value right above?