Keep record based on condition

[d0816]

Hello,

I Have following dataset. For an ID AND StartDate, I want to keep the record based on following criteria and order:

1. Keep a record with matching StartMonth and YearMonth. (For.eg. ID 4)

2. If matching start month is not available, then keep a record with most recent YearMonth.(For.eg. ID 2)

3. If matching start month is not available and only one YearMonth is available, then keep the record with that YearMonth (For.eg. ID 5)

(There are no missing values is all the variable and there are other variables than what is shown here in sample dataset.)

Dataset Have:

ID	StartDate	StartMonth	YearMonth	company	area
1	4/8/2019	2019-04	2019-09	aw	mke
1	4/30/2019	2019-04	2019-09	aw	mke
2	4/29/2019	2019-04	2019-07	wr	barron
2	4/29/2019	2019-04	2019-08	wr	eau claire
2	4/29/2019	2019-04	2019-09	wr	eau claire
3	6/17/2019	2019-06	2019-05	aw	mke
3	6/17/2019	2019-06	2019-06	aw	mke
3	6/17/2019	2019-06	2019-07	aw	mke
3	6/17/2019	2019-06	2019-08	aw	mke
3	6/17/2019	2019-06	2019-09	aw	mke
3	6/17/2019	2019-06	2019-04	um	mke
4	4/1/2019	2019-04	2019-03	ro	mke
4	4/1/2019	2019-04	2019-04	ro	mke
4	4/1/2019	2019-04	2019-05	ro	mke
4	4/1/2019	2019-04	2019-06	ro	mke
4	4/1/2019	2019-04	2019-07	ro	mke
4	4/1/2019	2019-04	2019-08	ro	mke
4	4/1/2019	2019-04	2019-09	ro	mke
4	6/5/2019	2019-06	2019-03	ro	mke
4	6/5/2019	2019-06	2019-04	ro	mke
4	6/5/2019	2019-06	2019-05	ro	mke
4	6/5/2019	2019-06	2019-06	ro	mke
4	6/5/2019	2019-06	2019-07	ro	mke
4	6/5/2019	2019-06	2019-08	ro	mke
4	6/5/2019	2019-06	2019-09	ro	mke
4	7/23/2019	2019-07	2019-03	ro	mke
4	7/23/2019	2019-07	2019-04	ro	mke
4	7/23/2019	2019-07	2019-05	ro	mke
4	7/23/2019	2019-07	2019-06	ro	mke
4	7/23/2019	2019-07	2019-07	ro	mke
4	7/23/2019	2019-07	2019-08	ro	mke
4	7/23/2019	2019-07	2019-09	ro	mke
5	5/1/2019	2019-05	2019-09	aw	mke
6	6/3/2019	2019-06	2019-04	ro	mke
6	6/3/2019	2019-06	2019-05	ro	mke
6	6/3/2019	2019-06	2019-06	ro	mke
6	6/3/2019	2019-06	2019-07	ro	mke
6	6/3/2019	2019-06	2019-08	ro	mke
6	6/3/2019	2019-06	2019-09	ro	mke
7	5/2/2019	2019-05	2019-03	fs	door
7	5/2/2019	2019-05	2019-04	fs	door
7	5/2/2019	2019-05	2019-05	fs	door
7	5/2/2019	2019-05	2019-06	fs	door
7	5/2/2019	2019-05	2019-07	fs	door
7	5/2/2019	2019-05	2019-08	fs	door
7	5/2/2019	2019-05	2019-09	fs	door
7	5/25/2019	2019-05	2019-03	fs	door
7	5/25/2019	2019-05	2019-04	fs	door
7	5/25/2019	2019-05	2019-05	fs	door
7	5/25/2019	2019-05	2019-06	fs	door
7	5/25/2019	2019-05	2019-07	fs	door
7	5/25/2019	2019-05	2019-08	fs	door
7	5/25/2019	2019-05	2019-09	fs	door
7	7/15/2019	2019-07	2019-03	fs	door
7	7/15/2019	2019-07	2019-04	fs	door
7	7/15/2019	2019-07	2019-05	fs	door
7	7/15/2019	2019-07	2019-06	fs	door
7	7/15/2019	2019-07	2019-07	fs	door
7	7/15/2019	2019-07	2019-08	fs	door
7	7/15/2019	2019-07	2019-09	fs	door

Dataset Want:

ID	StartDate	StartMonth	YearMonth	company	area
1	4/8/2019	2019-04	2019-09	aw	mke
1	4/30/2019	2019-04	2019-09	aw	mke
2	4/29/2019	2019-04	2019-09	wr	eau claire
3	6/17/2019	2019-06	2019-06	aw	mke
4	4/1/2019	2019-04	2019-04	ro	mke
4	6/5/2019	2019-06	2019-06	ro	mke
4	7/23/2019	2019-07	2019-07	ro	mke
5	5/1/2019	2019-05	2019-09	aw	mke
6	6/3/2019	2019-06	2019-06	ro	mke
7	5/2/2019	2019-05	2019-05	fs	door
7	5/25/2019	2019-05	2019-05	fs	door
7	7/15/2019	2019-07	2019-07	fs	door

Appreciate any help.

Thank you.

[PGStats]

This should do it:

data want;
do until(last.StartDate);
    set have; by id StartDate;
    if startMonth=yearMonth then match = 1;
    if yearMonth > mrym then mrym = yearMonth;
    end;
do until(last.StartDate);
    set have; by id StartDate;
    if match then do;
        if startMonth=yearMonth then output;
        end;
    else if yearMonth = mrym then output;
    end;
drop match mrym;
run;

[hashman]

@d0816:

I freely admit that I love the double-DoW-loop solution by @PGStats because (a) it aligns with my procedural programming proclivities and (b) one would be hard-pressed to find a more ardent proponent of the DoW-loop than myself. Just looking at the @PGStats program, I can understand its logic and what it's doing with the data better than any lengthy explanations.

The SQL program below also works to attain the same result (and doesn't require explicit sorting to boot - it sorts behind-the-scenes implicitly). However, (a) if I didn't write it myself, understanding what it's doing would be much harder for me compared to the DoW-loop, and (b) it doesn't guarantee that the output will retain the same relative record order.

data have ;                                                                                                                                                                                                                                                     
  input id startdate :mmddyy10. (startmonth yearmonth) (:$7.) ;                                                                                                                                                                                                 
  format startdate yymmdd10. ;                                                                                                                                                                                                                                  
  cards ;                                                                                                                                                                                                                                                       
1      4/8/2019       2019-04      2019-09                                                                                                                                                                                                                      
1      4/30/2019      2019-04      2019-09                                                                                                                                                                                                                      
2      4/29/2019      2019-04      2019-07                                                                                                                                                                                                                      
2      4/29/2019      2019-04      2019-08                                                                                                                                                                                                                      
2      4/29/2019      2019-04      2019-09                                                                                                                                                                                                                      
3      6/17/2019      2019-06      2019-05                                                                                                                                                                                                                      
3      6/17/2019      2019-06      2019-06                                                                                                                                                                                                                      
3      6/17/2019      2019-06      2019-07                                                                                                                                                                                                                      
3      6/17/2019      2019-06      2019-08                                                                                                                                                                                                                      
3      6/17/2019      2019-06      2019-09                                                                                                                                                                                                                      
3      6/17/2019      2019-06      2019-04                                                                                                                                                                                                                      
4      4/1/2019       2019-04      2019-03                                                                                                                                                                                                                      
4      4/1/2019       2019-04      2019-04                                                                                                                                                                                                                      
4      4/1/2019       2019-04      2019-05                                                                                                                                                                                                                      
4      4/1/2019       2019-04      2019-06                                                                                                                                                                                                                      
4      4/1/2019       2019-04      2019-07                                                                                                                                                                                                                      
4      4/1/2019       2019-04      2019-08                                                                                                                                                                                                                      
4      4/1/2019       2019-04      2019-09                                                                                                                                                                                                                      
4      6/5/2019       2019-06      2019-03                                                                                                                                                                                                                      
4      6/5/2019       2019-06      2019-04                                                                                                                                                                                                                      
4      6/5/2019       2019-06      2019-05                                                                                                                                                                                                                      
4      6/5/2019       2019-06      2019-06                                                                                                                                                                                                                      
4      6/5/2019       2019-06      2019-07                                                                                                                                                                                                                      
4      6/5/2019       2019-06      2019-08                                                                                                                                                                                                                      
4      6/5/2019       2019-06      2019-09                                                                                                                                                                                                                      
4      7/23/2019      2019-07      2019-03                                                                                                                                                                                                                      
4      7/23/2019      2019-07      2019-04                                                                                                                                                                                                                      
4      7/23/2019      2019-07      2019-05                                                                                                                                                                                                                      
4      7/23/2019      2019-07      2019-06                                                                                                                                                                                                                      
4      7/23/2019      2019-07      2019-07                                                                                                                                                                                                                      
4      7/23/2019      2019-07      2019-08                                                                                                                                                                                                                      
4      7/23/2019      2019-07      2019-09                                                                                                                                                                                                                      
5      5/1/2019       2019-05      2019-09                                                                                                                                                                                                                      
;                                                                                                                                                                                                                                                               
run ;                                                                                                                                                                                                                                                           
                                                                                                                                                                                                                                                                
proc sql ;                                                                                                                                                                                                                                                      
  create table want as                                                                                                                                                                                                                                          
  select * from have                                                                                                                                                                                                                                            
  where startmonth = yearmonth                                                                                                                                                                                                                                  
  union                                                                                                                                                                                                                                                         
  select * from have                                                                                                                                                                                                                                            
  where id not in (select id from have where startmonth = yearmonth)                                                                                                                                                                                            
  group id                                                                                                                                                                                                                                                      
  having yearmonth = max (yearmonth)                                                                                                                                                                                                                            
  ;                                                                                                                                                                                                                                                             
quit ;                                             

It's one of those rare cases when @PGStats would present a DATA step solution and I'd offer SQL. But for me, being rather SQL-maladroit, it's a useful exercise.

Kind regards

Paul D.

[FreelanceReinh]

@hashman: Always interesting to see different approaches. The sample data provided by the OP are a bit tricky in that they don't contain all possible cases. (Of course, I don't know what is really "possible" in that context.) With two small changes -- yearmonth='2019-08' (instead of -09) in the first observation and yearmonth='2019-02' (instead of -04) in the 13th (that is for ID 4, startdate 4/1/2019) -- I'm afraid your PROC SQL step would fail to select an observation for the two corresponding "ID-STARTDATE BY groups" because for the second table of the "union" only ID is used to identify groups of observations.

A simple fix (for that second table) would be

  select * from have
  where catx(',',id,startdate) not in (select catx(',',id,startdate) from have where startmonth = yearmonth)
  group id, startdate
  having yearmonth = max (yearmonth)

Another option:

  select a.* from have a left join have(where=(startmonth=yearmonth)) b
  on a.id=b.id & a.startdate=b.startdate
  where b.id=.
  group a.id, a.startdate
  having a.yearmonth = max (a.yearmonth)

In a test with a larger HAVE dataset (~300,000 obs., WANT ~71,000 obs.) these were equally fast (but slower than the DATA step), whereas a more concise correlated subquery (where not exists (...)) was extremely slow.

[d0816]

@PGStats @hashman and @FreelanceReinh,

Thank you all so much for responding.

The codes by @PGStats and @hashman both worked in the sample dataset I provided. But two codes returned me with different number of observations in my real dataset.  I compared the results of two codes in my real dataset and looked for IDs with different no. of records. Like @FreelanceReinh said, my sample dataset may not have contained all possible cases even though I strived for it. So I created new sample dataset (see below) from original dataset. Data step code is returning the result as intended. It looks like SQL code is picking most recent StartDate only for an ID. I will try the fix @FreelanceReinh suggested. Thanks.

data have_new ;

input id startdate :mmddyy10. (startmonth yearmonth) (:$7.) ;

format startdate yymmdd10. ;

cards ;

100 5/19/2019 2019-05 2019-06

100 5/19/2019 2019-05 2019-07

100 5/19/2019 2019-05 2019-08

100 5/19/2019 2019-05 2019-09

100 7/27/2019 2019-07 2019-06

100 7/27/2019 2019-07 2019-07

100 7/27/2019 2019-07 2019-08

100 7/27/2019 2019-07 2019-09

200 5/6/2019 2019-05 2019-06

200 5/6/2019 2019-05 2019-07

200 5/6/2019 2019-05 2019-08

200 5/6/2019 2019-05 2019-09

200 6/6/2019 2019-06 2019-06

200 6/6/2019 2019-06 2019-07

200 6/6/2019 2019-06 2019-08

200 6/6/2019 2019-06 2019-09

200 7/17/2019 2019-07 2019-06

200 7/17/2019 2019-07 2019-07

200 7/17/2019 2019-07 2019-08

200 7/17/2019 2019-07 2019-09

300 4/15/2019 2019-04 2019-07

300 4/15/2019 2019-04 2019-08

300 4/15/2019 2019-04 2019-09

300 7/24/2019 2019-07 2019-07

300 7/24/2019 2019-07 2019-08

300 7/24/2019 2019-07 2019-09

400 5/7/2019 2019-05 2019-06

400 5/7/2019 2019-05 2019-07

400 5/7/2019 2019-05 2019-08

400 5/7/2019 2019-05 2019-09

400 7/25/2019 2019-07 2019-06

400 7/25/2019 2019-07 2019-07

400 7/25/2019 2019-07 2019-08

400 7/25/2019 2019-07 2019-09

500 4/15/2019 2019-04 2019-05

500 4/15/2019 2019-04 2019-06

500 4/15/2019 2019-04 2019-07

500 4/15/2019 2019-04 2019-08

500 4/15/2019 2019-04 2019-09

500 6/12/2019 2019-06 2019-05

500 6/12/2019 2019-06 2019-06

500 6/12/2019 2019-06 2019-07

500 6/12/2019 2019-06 2019-08

500 6/12/2019 2019-06 2019-09

600 4/1/2019 2019-04 2019-07

600 4/1/2019 2019-04 2019-08

600 4/1/2019 2019-04 2019-09

600 7/29/2019 2019-07 2019-07

600 7/29/2019 2019-07 2019-08

600 7/29/2019 2019-07 2019-09

700 3/15/2019 2019-03 2019-05

700 3/15/2019 2019-03 2019-06

700 3/15/2019 2019-03 2019-07

700 3/15/2019 2019-03 2019-08

700 3/15/2019 2019-03 2019-09

700 6/14/2019 2019-06 2019-05

700 6/14/2019 2019-06 2019-06

700 6/14/2019 2019-06 2019-07

700 6/14/2019 2019-06 2019-08

700 6/14/2019 2019-06 2019-09

800 4/17/2019 2019-04 2019-07

800 4/17/2019 2019-04 2019-08

800 4/17/2019 2019-04 2019-09

800 7/10/2019 2019-07 2019-07

800 7/10/2019 2019-07 2019-08

800 7/10/2019 2019-07 2019-09

900 5/21/2019 2019-05 2019-07

900 5/21/2019 2019-05 2019-08

900 5/21/2019 2019-05 2019-09

900 7/31/2019 2019-07 2019-07

900 7/31/2019 2019-07 2019-08

900 7/31/2019 2019-07 2019-09

1000 6/17/2019 2019-06 2019-07

1000 6/17/2019 2019-06 2019-08

1000 6/17/2019 2019-06 2019-09

1000 7/12/2019 2019-07 2019-07

1000 7/12/2019 2019-07 2019-08

1000 7/12/2019 2019-07 2019-09

2000 3/12/2019 2019-03 2019-07

2000 3/12/2019 2019-03 2019-08

2000 3/12/2019 2019-03 2019-09

2000 7/29/2019 2019-07 2019-07

2000 7/29/2019 2019-07 2019-08

2000 7/29/2019 2019-07 2019-09

3000 3/18/2019 2019-03 2019-04

3000 3/18/2019 2019-03 2019-05

3000 3/18/2019 2019-03 2019-06

3000 3/18/2019 2019-03 2019-07

3000 3/18/2019 2019-03 2019-08

3000 3/18/2019 2019-03 2019-09

3000 5/9/2019 2019-05 2019-04

3000 5/9/2019 2019-05 2019-05

3000 5/9/2019 2019-05 2019-06

3000 5/9/2019 2019-05 2019-07

3000 5/9/2019 2019-05 2019-08

3000 5/9/2019 2019-05 2019-09

4000 3/23/2019 2019-03 2019-06

4000 3/23/2019 2019-03 2019-07

4000 3/23/2019 2019-03 2019-08

4000 3/23/2019 2019-03 2019-09

4000 7/13/2019 2019-07 2019-06

4000 7/13/2019 2019-07 2019-07

4000 7/13/2019 2019-07 2019-08

4000 7/13/2019 2019-07 2019-09

5000 4/1/2019 2019-04 2019-05

5000 4/1/2019 2019-04 2019-06

5000 4/1/2019 2019-04 2019-07

5000 4/1/2019 2019-04 2019-08

5000 4/1/2019 2019-04 2019-09

5000 6/26/2019 2019-06 2019-05

5000 6/26/2019 2019-06 2019-06

5000 6/26/2019 2019-06 2019-07

5000 6/26/2019 2019-06 2019-08

5000 6/26/2019 2019-06 2019-09

6000 3/23/2019 2019-03 2019-06

6000 3/23/2019 2019-03 2019-07

6000 3/23/2019 2019-03 2019-08

6000 3/23/2019 2019-03 2019-09

6000 4/16/2019 2019-04 2019-06

6000 4/16/2019 2019-04 2019-07

6000 4/16/2019 2019-04 2019-08

6000 4/16/2019 2019-04 2019-09

6000 7/2/2019 2019-07 2019-06

6000 7/2/2019 2019-07 2019-07

6000 7/2/2019 2019-07 2019-08

6000 7/2/2019 2019-07 2019-09

7000 3/8/2019 2019-03 2019-05

7000 3/8/2019 2019-03 2019-06

7000 3/8/2019 2019-03 2019-07

7000 3/8/2019 2019-03 2019-08

7000 3/8/2019 2019-03 2019-09

7000 7/2/2019 2019-07 2019-05

7000 7/2/2019 2019-07 2019-06

7000 7/2/2019 2019-07 2019-07

7000 7/2/2019 2019-07 2019-08

7000 7/2/2019 2019-07 2019-09

; run;