cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
BookmarkSubscribeRSS Feed
d0816
Quartz | Level 8 d0816
Quartz | Level 8

Keep record based on condition

Posted 09-19-2019 04:01 PM (699 views)

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:

IDStartDateStartMonthYearMonthcompanyarea
14/8/20192019-042019-09awmke
14/30/20192019-042019-09awmke
24/29/20192019-042019-07wrbarron
24/29/20192019-042019-08wreau claire
24/29/20192019-042019-09wreau claire
36/17/20192019-062019-05awmke
36/17/20192019-062019-06awmke
36/17/20192019-062019-07awmke
36/17/20192019-062019-08awmke
36/17/20192019-062019-09awmke
36/17/20192019-062019-04ummke
44/1/20192019-042019-03romke
44/1/20192019-042019-04romke
44/1/20192019-042019-05romke
44/1/20192019-042019-06romke
44/1/20192019-042019-07romke
44/1/20192019-042019-08romke
44/1/20192019-042019-09romke
46/5/20192019-062019-03romke
46/5/20192019-062019-04romke
46/5/20192019-062019-05romke
46/5/20192019-062019-06romke
46/5/20192019-062019-07romke
46/5/20192019-062019-08romke
46/5/20192019-062019-09romke
47/23/20192019-072019-03romke
47/23/20192019-072019-04romke
47/23/20192019-072019-05romke
47/23/20192019-072019-06romke
47/23/20192019-072019-07romke
47/23/20192019-072019-08romke
47/23/20192019-072019-09romke
55/1/20192019-052019-09awmke
66/3/20192019-062019-04romke
66/3/20192019-062019-05romke
66/3/20192019-062019-06romke
66/3/20192019-062019-07romke
66/3/20192019-062019-08romke
66/3/20192019-062019-09romke
75/2/20192019-052019-03fsdoor
75/2/20192019-052019-04fsdoor
75/2/20192019-052019-05fsdoor
75/2/20192019-052019-06fsdoor
75/2/20192019-052019-07fsdoor
75/2/20192019-052019-08fsdoor
75/2/20192019-052019-09fsdoor
75/25/20192019-052019-03fsdoor
75/25/20192019-052019-04fsdoor
75/25/20192019-052019-05fsdoor
75/25/20192019-052019-06fsdoor
75/25/20192019-052019-07fsdoor
75/25/20192019-052019-08fsdoor
75/25/20192019-052019-09fsdoor
77/15/20192019-072019-03fsdoor
77/15/20192019-072019-04fsdoor
77/15/20192019-072019-05fsdoor
77/15/20192019-072019-06fsdoor
77/15/20192019-072019-07fsdoor
77/15/20192019-072019-08fsdoor
77/15/20192019-072019-09fsdoor

 

Dataset Want:

IDStartDateStartMonthYearMonthcompanyarea
14/8/20192019-042019-09awmke
14/30/20192019-042019-09awmke
24/29/20192019-042019-09wreau claire
36/17/20192019-062019-06awmke
44/1/20192019-042019-04romke
46/5/20192019-062019-06romke
47/23/20192019-072019-07romke
55/1/20192019-052019-09awmke
66/3/20192019-062019-06romke
75/2/20192019-052019-05fsdoor
75/25/20192019-052019-05fsdoor
77/15/20192019-072019-07fsdoor

 

Appreciate any help.

 

Thank you.

0 Likes
Reply
4 REPLIES 4
PGStats
Opal | Level 21 PGStats
Opal | Level 21

Re: Keep record based on condition

Posted 09-19-2019 06:20 PM (661 views)  |   In reply to d0816

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;
PG
Reply
hashman
Ammonite | Level 13 hashman
Ammonite | Level 13

Re: Keep record based on condition

Posted 09-19-2019 10:00 PM (642 views)  |   In reply to d0816

@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.

 

     

Reply
FreelanceReinh
Jade | Level 19 FreelanceReinh
Jade | Level 19

Re: Keep record based on condition

Posted 09-20-2019 10:50 AM (610 views)  |   In reply to hashman

@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.

Reply
d0816
Quartz | Level 8 d0816
Quartz | Level 8

Re: Keep record based on condition

Posted 09-20-2019 11:13 AM (608 views)  |   In reply to FreelanceReinh

@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;

 

0 Likes
Reply

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

How to Concatenate Values

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

Watch How to Concatenate Values on YouTube

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
  • 4 replies
  • ‎09-19-2019 04:01 PM
  • 700 views
  • 4 likes
  • 4 in conversation