Find Latest value using ROW_NUMBER OVER (PARTITION BY 'X' ORDER BY DATE) AS Rank in SAS

[pcfriendly]

Hi Bruno,

My options to choose under the Data area in the Rank icon are as follows;

• Job_Role sort order - which I've set to Descending
• Columns to Rank - Start_Date
• Rank By - Job_Role
• Sort by Variables checkbox - not sure if I should leave it checked?

My options to choose under the Options area in the Rank icon are as follows;

• Ranking method - Smallest to Largest Radio Button
• If values tie, use; - Default is Median, other options are 'High Rank', 'Low Rank'
• There's also a checkbox for 'Reverse Ranking from largest to smallest' doesn't this negate the Ranking Method already set to 'Smallest to Largest?

The date field I'm using is a Date Time value & I'm getting results like 403.5 in my ranking results??

[luo]

Use subquery. such as below

proc sql;

select c1.make,
	c1.model,
	c1.invoice,
	(
		select count(*)
		from sashelp.cars c2
		where c2.make=c1.make
		and c2.invoice>=c1.invoice
	) as rankings
from sashelp.cars c1
order by c1.make,c1.invoice DESC
;

run;

[pcfriendly]

Thanks to all, especially, Reeza & Bruno.

I eventually figured out to do the Rank using only the 2 Fields & got the Rank Number & then ran a query builder to select where the Rank Number = 1 as you said Bruno.

This will come in very handy, Cheers

Peter

[HRI]

you can use proc rank to rank your data first, then using the rank to find first, second, .... or last one.

[Eurisko]

It's all well and good that you can rank using the menu system, however how would this translate to code?

[SamuelD]

Hi there,

When i hav a challenge working out the code i use the GUI and then click on the code tab once executed to see what SAS EG does.

The ranking code would look like this:

PROC SORT
	DATA=inputdata  
	OUT=sorted_data_set
	;
	BY {customer number};
RUN;
PROC RANK DATA = sorted_data_set
	DESCENDING /* use decending if using dates so the most current record is a 1 */
	TIES=LOW
	OUT=WORK.RankedData;
	BY {customer number};
	VAR {date};
RANKS ranking_number;
RUN;
QUIT;

You may need to do query after this to filter the output so you are selecting records that have a rank of 1.

Hope this helps.