Proc SQL Doing a Rank syntax

[Jyuen204]

I have a table where I have an AGENT_ID, AGENT_NAME, TEAM_LEAD_NM, DATE

I am trying to use Proc SQL to generate a rank because the ID and NAME are duplicated.

proc sql;
select AGENT_ID, AGENT_NAME, TEAM_LEAD_NM, rank() over (Partition by AGENT_LAN_ID
                                                                                                             order by sys_load_dtm desc) as RANK
from TABLE
where AGENT_NAME = 'Smith, John';
run;

The error i get is:

____
22
76
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, ',', -, /, <, <=, <>, =, >, >=, ?, AND, BETWEEN,
CONTAINS, EQ, EQT, GE, GET, GT, GTT, LE, LET, LIKE, LT, LTT, NE, NET, OR, ^=, |, ||, ~=.

ERROR 76-322: Syntax error, statement will be ignored.

what is the proper syntax in PROC SQL to rank my data? 

Thank you!

[PaigeMiller]

Use PROC RANK and not PROC SQL

https://documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.4&docsetId=proc&docsetTarget=p0le3p5...

[Jyuen204]

I am trying to build a table of of this select statement.

[PeterClemmensen]

Rank over is not available in PROC SQL.

[Jyuen204]

Is there any other method in doing this within SAS?

[PaigeMiller]

---

@Jyuen204 wrote:

Is there any other method in doing this within SAS?

---

Yes I mentioned a method in my earlier reply and provided a link to the documentation (which includes examples of code)

[ballardw]

---

@Jyuen204 wrote:

Is there any other method in doing this within SAS?

---

Almost certainly more than one.

It might help to show some starting data and what you expect the result to be when finished.

[Jyuen204]

ID          Name               Manager           DATE

XXX1     Smith, John     Doe, Jane         15NOV2019

XXX1     Smith, John     Doe, Jane         13NOV2019

XXX1     Smith, John     Crane, Frasier   3NOV2019

Want to get the following out put in a another table

ID          Name               Manager           DATE              RANK

XXX1     Smith, John     Doe, Jane         15NOV2019    1

XXX1     Smith, John     Doe, Jane         13NOV2019    2

XXX1     Smith, John     Crane, Frasier   3NOV2019      3

[PaigeMiller]

Hi

I'm afraid this doesn't explain anything. How are these rankings computed? 

Or are they simply sequence numbers, not true rankings?

[Jyuen204]

That's the data within my table, I need to rank the data an place into another table (in the ranked format above.
I am ranking by date descending

[PaigeMiller]

So you are essentially asking for sequence numbers? (assuming you have sorted the data by descending date).

[Jyuen204]

Well my data isnt as clean as I am going to have multiple names and multiple dates

[PaigeMiller]

---

@Jyuen204 wrote:
Well my data isnt as clean as I am going to have multiple names and multiple dates

---

This doesn't explain anything, and it doesn't answer my question.

Give us a small example of the un-clean data, and show us and explain to us what you want to do with it.

[Tom]

---

@Jyuen204 wrote:

ID          Name               Manager           DATE

XXX1     Smith, John     Doe, Jane         15NOV2019

XXX1     Smith, John     Doe, Jane         13NOV2019

XXX1     Smith, John     Crane, Frasier   3NOV2019

Want to get the following out put in a another table

ID          Name               Manager           DATE              RANK

XXX1     Smith, John     Doe, Jane         15NOV2019    1

XXX1     Smith, John     Doe, Jane         13NOV2019    2

XXX1     Smith, John     Crane, Frasier   3NOV2019      3

---

What is your definition of RANK?  Are you just counting observations?

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

Counting distinct dates?

data want;
  set have;
  by id descending date;
  if first.id then rank=0;
  rank+first.date;
run;

[Jyuen204]

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