Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Home
- /
- Programming
- /
- SAS Procedures
- /
- MAX with a conditional By group?

Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

🔒 This topic is **solved** and **locked**.
Need further help from the community? Please
sign in and ask a **new** question.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Posted 01-26-2017 12:02 PM
(1153 views)

Hi All,

I ran into a problem where I have the following table:

**Year Year_L**

2000 2000

2001

2002

2003

2004 2004

2005

2006

2007

2008 2008

2009 2009

2010

2011

and I want

**Year Year_L**

2000 2000

2001 2000

2002 2000

2003 2000

2004 2004

2005 2004

2006 2004

2007 2004

2008 2008

2009 2009

2010 2009

2011 2009

Basically I want all missing fields to take all previous latest Year_L.

Note that I cannot use lags since data is too disorganized to have it sorted (and it's not quite in my control to make them more organized)

I thought about using max(Year_L) but I'm not sure how to set the "By GROUPS" so that it would take current year > year.

Thank you so much!

1 ACCEPTED SOLUTION

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Well, something like:

data want; set have (rename=(year_l=tmp)); retain year_l; if tmp ne "" then year_l=tmp; run;

Note, I assume your data is character format as you have not posted the test data in a datastep.

7 REPLIES 7

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Well, something like:

data want; set have (rename=(year_l=tmp)); retain year_l; if tmp ne "" then year_l=tmp; run;

Note, I assume your data is character format as you have not posted the test data in a datastep.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Hi,

Thank you for your solution!

I am wondering is there a way to make it work if its not in order? say if 2001 comes before the row of 2000.

Thank you once again!

Thank you for your solution!

I am wondering is there a way to make it work if its not in order? say if 2001 comes before the row of 2000.

Thank you once again!

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

From your given example, you would sort by col1 then apply that code. If there's no logically order, not sure how you could logically apply any condition. Try posting test data in the form of a datastep which accurately describes your problem. Follow this if you stuck:

Left for the day, will see responses tomorrow.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

@chrischoi614 wrote:

I am wondering is there a way to make it work if its not in order? say if 2001 comes before the row of 2000.

If you have a more complex need you shoud provide example input, output and any actual rules for determining assignment.

For example I am not sure which variable you mean to address that comes before 2000.

It may be that the solution is to sort the data first and then apply the existing solution.

Is the case you are thinking like this?

**Year Year_L**

2001

2000 2000

2002

2003

2004 2004

2005

2006

If so, what value should Year_L have and how do we know that it should get that value?

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

The variables actually mean Year and Year of loss. IF there is a loss, Year_L will be populated with the same year.

so since in 2000, there is a loss, in 2001, the latest loss up to 2001 is 2000. Therefore 2001 should have Year_L = 2000. Hope that explains the logic.

Thank you so much!

so since in 2000, there is a loss, in 2001, the latest loss up to 2001 is 2000. Therefore 2001 should have Year_L = 2000. Hope that explains the logic.

Thank you so much!

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

@chrischoi614 wrote:

The variables actually mean Year and Year of loss. IF there is a loss, Year_L will be populated with the same year.

so since in 2000, there is a loss, in 2001, the latest loss up to 2001 is 2000. Therefore 2001 should have Year_L = 2000. Hope that explains the logic.

Thank you so much!

That really sounds like Sort the data and apply the previous solution should work.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Thank you! I will probably just do that.

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

**If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. **

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.