topic Re: Proc SQL Doing a Rank syntax in SAS Programming

Proc SQL Doing a Rank syntax

Jyuen204 — Wed, 20 Nov 2019 18:00:35 GMT

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!

Re: Proc SQL Doing a Rank syntax

PaigeMiller — Wed, 20 Nov 2019 18:07:54 GMT

Use PROC RANK and not PROC SQL

https://documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.4&docsetId=proc&docsetTarget=p0le3p5ngj1zlbn1mh3tistq9t76.htm&locale=en

Re: Proc SQL Doing a Rank syntax

Jyuen204 — Wed, 20 Nov 2019 18:11:34 GMT

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

Re: Proc SQL Doing a Rank syntax

PeterClemmensen — Wed, 20 Nov 2019 18:14:41 GMT

Rank over is not available in PROC SQL.

Re: Proc SQL Doing a Rank syntax

Jyuen204 — Wed, 20 Nov 2019 18:21:07 GMT

Is there any other method in doing this within SAS?

Re: Proc SQL Doing a Rank syntax

PaigeMiller — Wed, 20 Nov 2019 18:24:00 GMT

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

Re: Proc SQL Doing a Rank syntax

ballardw — Wed, 20 Nov 2019 18:26:03 GMT

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

Re: Proc SQL Doing a Rank syntax

Jyuen204 — Wed, 20 Nov 2019 18:32:36 GMT

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

Re: Proc SQL Doing a Rank syntax

PaigeMiller — Wed, 20 Nov 2019 18:42:16 GMT

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

Or are they simply sequence numbers, not true rankings?

Re: Proc SQL Doing a Rank syntax

Jyuen204 — Wed, 20 Nov 2019 18:54:41 GMT

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

Re: Proc SQL Doing a Rank syntax

PaigeMiller — Wed, 20 Nov 2019 18:58:44 GMT

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

Re: Proc SQL Doing a Rank syntax

Jyuen204 — Wed, 20 Nov 2019 19:01:53 GMT

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

Re: Proc SQL Doing a Rank syntax

Tom — Wed, 20 Nov 2019 19:06:27 GMT

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

Re: Proc SQL Doing a Rank syntax

Jyuen204 — Wed, 20 Nov 2019 19:09:14 GMT

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

Re: Proc SQL Doing a Rank syntax

PaigeMiller — Wed, 20 Nov 2019 19:10:30 GMT

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

Re: Proc SQL Doing a Rank syntax

Tom — Wed, 20 Nov 2019 19:11:33 GMT

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

Re: Proc SQL Doing a Rank syntax

novinosrin — Wed, 20 Nov 2019 19:12:20 GMT

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

Re: Proc SQL Doing a Rank syntax

PaigeMiller — Wed, 20 Nov 2019 19:27:35 GMT

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

Re: Proc SQL Doing a Rank syntax

Jyuen204 — Wed, 20 Nov 2019 19:34:14 GMT

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

Re: Proc SQL Doing a Rank syntax

Jyuen204 — Wed, 20 Nov 2019 19:36:09 GMT

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