Proc SQL Doing a Rank syntax

[Tom]

---

@Jyuen204 wrote:

TABLE1

ID          Name               Manager           DATE

XXX1     Smith, John     Doe, Jane         15NOV2019

XXX1     Smith, John     Doe, Jane         13NOV2019

XXX1     Smith, John     Crane, Frasier   3NOV2019

I am needing to rank a table of data like here. so that the most recent date is ranked the highest.

I need to create a secondary table of this data where RANK = 1

so of the data examples above, I want to rank the data in the table, and create a table of the rank 1 rows.

So I am only left with :
XXX1   Smith, John    Doe, Jane

---

Yet another reason not to use SQL. With a data step you can generate two datasets in one step (if you really need them).

data all most_recent;
  set have;
  by id descending date;
  if first.id then rank=0;
  rank+1;
  if rank=1 then output most_recent;
  output all;
run;

[Jyuen204]

If my table is names TableX how do i reference this in your example to get the data?

[novinosrin]

Hi @Jyuen204  If I understand you correctly, you want to compute rank using PROC SQL ?

data have;
input ID     $      Name     & $20.         Manager & $20. DATE:date9.;
format date date9.;
cards;
XXX1     Smith, John     Doe, Jane         15NOV2019
XXX1     Smith, John     Doe, Jane         13NOV2019
XXX1     Smith, John     Crane, Frasier   3NOV2019
;

proc sql;
create table want as
select a.*,count(b.date) as rank
from have a, have b
where a.id=b.id and a.date<=b.date
group by a.id,a.name,a.manager,a.date
order by id,rank;
quit;

proc print noobs;run;

ID	Name	Manager	DATE	rank
XXX1	Smith, John	Doe, Jane	15NOV2019	1
XXX1	Smith, John	Doe, Jane	13NOV2019	2
XXX1	Smith, John	Crane, Frasier	03NOV2019	3

[PaigeMiller]

---

@novinosrin wrote:

Hi @Jyuen204  If I understand you correctly, you want to compute rank using PROC SQL ?

data have;
input ID     $      Name     & $20.         Manager & $20. DATE:date9.;
format date date9.;
cards;
XXX1     Smith, John     Doe, Jane         15NOV2019
XXX1     Smith, John     Doe, Jane         13NOV2019
XXX1     Smith, John     Crane, Frasier   3NOV2019
;

proc sql;
create table want as
select a.*,count(b.date) as rank
from have a, have b
where a.id=b.id and a.date<=b.date
group by a.id,a.name,a.manager,a.date
order by id,rank;
quit;

proc print noobs;run;

ID	Name	Manager	DATE	rank
XXX1	Smith, John	Doe, Jane	15NOV2019	1
XXX1	Smith, John	Doe, Jane	13NOV2019	2
XXX1	Smith, John	Crane, Frasier	03NOV2019	3

---

What about the case of ties, where two records have the same date? I'm not sure I want to use this code in the case of ties, as I don't like the results.

Of course, the original poster ought to weigh in on this, and specify if ties can happen, and what should happen in the case of ties. PROC RANK gives you many options to handle ties.

[novinosrin]

Sir @PaigeMiller  True and agreed that Proc SQL is probably no robust nor best suited for the stated objective, however the ties can be handled with some extra logic,/syntax yet I admit not so neat like the solution suggested by you or Tom

So a simple "Distinct" in case of simple ties on dates, assuming OP's sample is a good representative of his/her real

select a.*,count(distinct b.date) as rank

alone will do. So,

data have;
input ID     $      Name     & $20.         Manager & $20. DATE:date9.;
format date date9.;
cards;
XXX1     Smith, John     Doe, Jane         15NOV2019
XXX1     Smith, John     Doe, Jane         15NOV2019
XXX1     Smith, John     Doe, Jane         13NOV2019
XXX1     Smith, John     Doe, Jane         13NOV2019
XXX1     Smith, John     Doe, Jane         13NOV2019
XXX1     Smith, John     Crane, Frasier   3NOV2019
XXX1     Smith, John     Crane, Frasier   3NOV2019
;

proc sql;
create table want as
select a.*,count(distinct b.date) as rank
from have a, have b
where a.id=b.id and a.date<=b.date
group by a.id,a.name,a.manager,a.date
order by id,rank;
quit;

proc print noobs;run;

[Jyuen204]

i am assuming "have" is the table name you are using in reference to my TABLE1?

[novinosrin]

Hi @Jyuen204   HAVE is the input sample table/dataset you or other Original posters provide us with., WANT is the expected output or the target to accomplish. This is just how the forum questions are framed.