BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
KS99
Obsidian | Level 7

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 -, 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
PaigeMiller
Diamond | Level 26
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.

--
Paige Miller

View solution in original post

2 REPLIES 2
PaigeMiller
Diamond | Level 26
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.

--
Paige Miller
KS99
Obsidian | Level 7
Thank you, Sir,
It works perfectly!

hackathon24-white-horiz.png

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 2 replies
  • 1795 views
  • 0 likes
  • 2 in conversation