topic Good method for selecting observations based on min or max date in SAS Programming

Good method for selecting observations based on min or max date

supp — Mon, 16 Sep 2019 12:24:27 GMT

Hello SAS community,

I have come across this scenario a few times and don't feel I have found or created a good solution. Lets say we have data as such:

data have;
	infile datalines;
		input personID $ attribute1 $ qty  date yymmdd10.;
		format date yymmdd10.;
datalines;
A m1 10 2019-03-01
A m1 20 2019-03-01
A m1 20 2019-02-01
A m2 20 2019-01-01
A m3 20 2019-02-01
B m3 10 2019-02-15
B m3 20 2019-02-15
C m2 20 2019-01-15
D m1 20 2019-01-15
D m1 20 2019-02-15
;
run;

We can have multiple observations per person per attribute. We want to return one observation per person per attribute with the earliest date, min(date). In the event there are multiple observations for the same person and attribute then select for the largest qty, max(qty).

Desired output:

A m1 20 2019-02-01
A m2 20 2019-01-01
A m3 20 2019-02-01
B m3 20 2019-02-15
C m2 20 2019-01-15
D m1 20 2019-01-15

For starters I tried this to return one row per person per attribute with the earliest date, but it doesn't work. As expected I get the note: NOTE: The query requires remerging summary statistics back with the original data.

proc sql;
	/** return one row per person and attribute **/
	create table want1 as
		select *
		from work.have
		group by personID, attribute1
		having min(date);
quit;

Is there a simple solution for this that I am not seeing? Is SQL the best approach? Or is there a better approach using a data step?

Thanks in advance!

edit: fixed first obs in desired output

Re: Good method for selecting observations based on min or max date

Reeza — Fri, 13 Sep 2019 16:53:41 GMT

Easy and boring with a data step.

Sort by person, date and descending quantity so the maxiumum quantity is first with multiple dates and then take the first record of each person/attribute group.

EDIT: Forgot attribute, added that in.

proc sort data=have;
by personID attribute date descending qt1;
run;

data want;
set have;
by personID attribute date;
if first.attribute;
run;

@supp wrote:

Hello SAS community,

I have come across this scenario a few times and don't feel I have found or created a good solution. Lets say we have data as such:
data have;
	infile datalines;
		input personID $ attribute1 $ qty  date yymmdd10.;
		format date yymmdd10.;
datalines;
A m1 10 2019-03-01
A m1 20 2019-03-01
A m1 20 2019-02-01
A m2 20 2019-01-01
A m3 20 2019-02-01
B m3 10 2019-02-15
B m3 20 2019-02-15
C m2 20 2019-01-15
D m1 20 2019-01-15
D m1 20 2019-02-15
;
run;
We can have multiple observations per person per attribute. We want to return one observation per person per attribute with the earliest date, min(date). In the event there are multiple observations for the same person and attribute then select for the largest qty, max(qty).

Desired output:
A m1 20 2019-03-01
A m2 20 2019-01-01
A m3 20 2019-02-01
B m3 20 2019-02-15
C m2 20 2019-01-15
D m1 20 2019-01-15
For starters I tried this to return one row per person per attribute with the earliest date, but it doesn't work. As expected I get the note: NOTE: The query requires remerging summary statistics back with the original data.
proc sql;
	/** return one row per person and attribute **/
	create table want1 as
		select *
		from work.have
		group by personID, attribute1
		having min(date);
quit;
Is there a simple solution for this that I am not seeing? Is SQL the best approach? Or is there a better approach using a data step?

Thanks in advance!

Re: Good method for selecting observations based on min or max date

novinosrin — Fri, 13 Sep 2019 16:56:24 GMT

Hi @supp The way you frame questions is very intriguing and nice. This time, i am just gonna correct your SQL and wait for others to respond with their innovative answers. I am gonna see if I can test myself i.e my own capability to come up with something that others haven't. But mind you, if somebody among the likes of PGstats, Tom, Paul D, Reinhard, John King, Xia Keshan et al etc has answered. I am just gonna take that notes. 🙂 and be in awe.

You missed an equality operator to filter the group having identified the MIN

proc sql;
	/** return one row per person and attribute **/
	create table want1 as
		select *
		from work.have
		group by personID, attribute1
		having min(date)=date;
quit;

Re: Good method for selecting observations based on min or max date

PGStats — Fri, 13 Sep 2019 16:55:30 GMT

Use a subquery:

proc sql;
create table want as
select *
from (select * from have group by personId, attribute1 having date = min(date))
group by personId, attribute1, date
having qty = max(qty);
quit;

Re: Good method for selecting observations based on min or max date

novinosrin — Fri, 13 Sep 2019 17:22:08 GMT

Does this work?

proc sql;
create table want as
 select *
	from work.have
     group by personID, attribute1
	having max(qty)=qty and min(date)=date;
quit;

Re: Good method for selecting observations based on min or max date

PGStats — Fri, 13 Sep 2019 17:27:38 GMT

@novinosrin This can't always work because the max(qty) might not occur on the first date.

Re: Good method for selecting observations based on min or max date

novinosrin — Fri, 13 Sep 2019 17:29:02 GMT

Thank you Sir @PGStats Great catch

Re: Good method for selecting observations based on min or max date

supp — Fri, 13 Sep 2019 17:36:36 GMT

@PGStats, you are correct. @novinosrin, I think the solution worked in the example I provided because all my scenarios had the max qty on the earliest date per person per attribute. I added person E to the data such that the max qty is not on the earliest date:

data have;
	infile datalines;
		input personID $ attribute1 $ qty  date yymmdd10.;
		format date yymmdd10.;
datalines;
A m1 10 2019-03-01
A m1 20 2019-03-01
A m1 20 2019-02-01
A m2 20 2019-01-01
A m3 20 2019-02-01
B m3 10 2019-02-15
B m3 20 2019-02-15
C m2 10 2019-01-15
D m1 20 2019-01-15
D m1 20 2019-02-15
E m2 30 2019-02-01
E m2 20 2019-01-01
;
run;


proc sql;
	/** return one row per person and attribute **/
	create table want1 as
		select *
		from work.have
		group by personID, attribute1
		having min(date)=date and qty=max(qty);
quit;

We don't return any observations for person E. Want to return the observation with the earliest date:

A	m1	20	2019-02-01
A	m2	20	2019-01-01
A	m3	20	2019-02-01
B	m3	20	2019-02-15
C	m2	10	2019-01-15
D	m1	20	2019-01-15

Re: Good method for selecting observations based on min or max date

Reeza — Fri, 13 Sep 2019 17:37:37 GMT

It depends on the criteria. I read that if records with multiple minimum dates the first time I coded it, or it could be the max if multiple overall which does change the question a bit.

Re: Good method for selecting observations based on min or max date

supp — Fri, 13 Sep 2019 17:37:56 GMT

Thanks for catching my syntax error. That actually helped a lot!

Re: Good method for selecting observations based on min or max date

novinosrin — Fri, 13 Sep 2019 18:12:12 GMT

That's the reason i don't like PG because he is Prodigygeniusstats aka pgstats 🙂

Re: Good method for selecting observations based on min or max date

supp — Fri, 13 Sep 2019 18:23:13 GMT

Hi @supp The way you frame questions is very intriguing and nice.

@novinosrin , if you have any suggestions on how to improve asking these questions let me know!

Re: Good method for selecting observations based on min or max date

novinosrin — Fri, 13 Sep 2019 18:33:27 GMT

You are doing great. We love it!

Re: Good method for selecting observations based on min or max date

FreelanceReinh — Sat, 14 Sep 2019 12:06:00 GMT

Hi @supp,

A third competitor besides PROC SQL (using GROUP BY, HAVING and nested subqueries) and the combination of PROC SORT and a DATA step (using BY and FIRST.by-variable or LAST.by-variable) is PROC SUMMARY (or its twin PROC MEANS):

proc summary data=have nway;
class personID attribute1;
output out=want(drop=_:) idgrp(min(date) max(qty) out (date qty)=);
run;

If the input dataset is sorted by the grouping variables, which is the case in your sample data, you can replace the CLASS statement with a BY statement and omit the NWAY option:

proc summary data=have;
by personID attribute1;
output out=want(drop=_:) idgrp(min(date) max(qty) out (date qty)=);
run;

I always have difficulties remembering the syntax of the IDGROUP (alias IDGRP) specification, but actually it's quite logical: Within the BY group (or combination of CLASS variable values) take the min(date) and, if there are ties, use max(qty) as a tiebreaker (and if there are still ties left, use the first of the tied observations in the input dataset -- or the last if the keyword LAST is inserted before OUT). The list of ID variables, (date qty), could include more variables if desired (e.g. all remaining variables of HAVE, if any).

Like the PROC SORT/DATA step approach PROC SUMMARY guarantees that only one observation per group will be included in the output dataset (see tiebreaking rules), whereas the HAVING clause of PROC SQL is prone to include tied observations (which may or may not be desirable, depending on the task).

It's also easily extendable to more than just two ID variables (date, qty). PROC SQL, however, would require additional levels of nested subqueries because the minimum/maximum specifications refer to different groupings and therefore require different GROUP BY clauses.

PROC SUMMARY shares with PROC SQL the advantages of doing everything in one step and not requiring sorted/indexed input data.

(I think the first date in the "desired output" in your initial post should read 2019-02-01.)

Re: Good method for selecting observations based on min or max date

hashman — Sun, 15 Sep 2019 17:13:19 GMT

@supp:

From your description, it looks as though the date in the first output record should rather be 2019-02-01, as it precedes 2019-03-01 for the same ID "A" and attribute "m1".

Re: Good method for selecting observations based on min or max date

hashman — Sun, 15 Sep 2019 21:51:02 GMT

@supp:

You've got a cornucopia of fine solutions, so methought another one, based on the hash object's prowess, wouldn't hurt at least from the standpoint of learning how it can be done. Note that like MEANS/SUMMARY it doesn't need the input file to be sorted, so I use it unsorted and have also added another satellite variable, VAR, to see if it gets transplanted from the input to the output correctly (it does). Despite the unsorted input, the output comes out sorted (again sharing the trait with MEANS/SUMMARY).

data have ;                                                                                                                             
  input personID $ attribute1 $ qty  date yymmdd10. var ;                                                                               
  format date yymmdd10. ;                                                                                                               
  cards ;                                                                                                                               
A m3 20 2019-02-01  1                                                                                                                   
B m3 10 2019-02-15  2                                                                                                                   
A m1 20 2019-03-01  3                                                                                                                   
B m3 20 2019-02-15  4                                                                                                                   
A m1 20 2019-02-01  5                                                                                                                   
C m2 20 2019-01-15  6                                                                                                                   
D m1 20 2019-01-15  7                                                                                                                   
A m1 10 2019-03-01  8                                                                                                                   
D m1 20 2019-02-15  9                                                                                                                   
A m2 20 2019-01-01 10                                                                                                                   
;                                                                                                                                       
run;                                                                                                                                    
                                                                                                                                        
data _null_ ;                                                                                                                           
  dcl hash h (dataset:"have(obs=0)", ordered:"a") ;                                                                                     
  h.definekey  ("personID", "attribute1") ;                                                                                             
  h.definedata (all:"y") ;                                                                                                              
  h.definedone () ;                                                                                                                     
  do until (z) ;                                                                                                                        
    set have (rename = (qty=_q date=_d var=_v)) end = z ;                                                                               
    if not (h.find() or (_d < date or _d = date and _q > qty)) then continue ;                                                          
    date = _d ;                                                                                                                         
    qty  = _q ;                                                                                                                         
    var  = _v ;                                                                                                                         
    h.replace() ;                                                                                                                       
  end ;                                                                                                                                 
  h.output (dataset:"want") ;                                                                                                           
  format date yymmdd10. ;                                                                                                               
run ;

Output:

person                                          
  ID      attribute1    qty          date    var
------------------------------------------------
  A           m1         20    2019-02-01      5
  A           m2         20    2019-01-01     10
  A           m3         20    2019-02-01      1
  B           m3         20    2019-02-15      4
  C           m2         20    2019-01-15      6
  D           m1         20    2019-01-15      7

Frankly, I prefer the brevity of @FreelanceReinh's MEANS/SUMMARY offering, except for one subtlety: With the hash, bringing another satellite variable like VAR above is a simple business and it works properly. I've tried to do the same by adding the ID statement in SUMMARY; but the values of VAR it generated came out incorrectly in the sense that not all of them aligned with the corresponding output (date,qty) pairs. Hope @FreelanceReinh will chime in on that.

Kind regards

Paul D.

Re: Good method for selecting observations based on min or max date

FreelanceReinh — Sun, 15 Sep 2019 22:29:10 GMT

@hashman wrote:
Frankly, I prefer the brevity of @FreelanceReinh's MEANS/SUMMARY offering, except for one subtlety: With the hash, bringing another satellite variable like VAR above is a simple business and it works properly. I've tried to do the same by adding the ID statement in SUMMARY; but the values of VAR it generated came out incorrectly in the sense that not all of them aligned with the corresponding output (date,qty) pairs. Hope @FreelanceReinh will chime in on that.

Hi Paul,

Thanks. It's true that adding the ID statement to the suggested code can bring in (unwanted) values from a different observation. What I alluded to in my post was adding more ID variables to the existing list (date qty) in the IDGRP specification, like (date qty var1 var2). In this case the results were correct at least in the tests I performed before posting my suggestion.

Re: Good method for selecting observations based on min or max date

hashman — Sun, 15 Sep 2019 23:41:33 GMT

@FreelanceReinh:

Aha! I see. Thanks for the prompt reply!

Kind regards

Paul D.

Re: Good method for selecting observations based on min or max date

supp — Mon, 16 Sep 2019 12:26:03 GMT

Good catch @hashman. I have corrected my oiginal post.