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!

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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