topic Re: Selecting limited observartions for each customer in SAS Enterprise Guide

Selecting limited observartions for each customer

deleted_user — Mon, 09 Oct 2006 00:41:29 GMT

I have the case where in a particular table I have customers who might have more than one record (eg Orders). I am wanting to do a query/data step on this table so that I can retrieve only the three most recent records. The table has a timestamp (datetime format) variable that will allow me to sort by date, however I don't know how to limit it to only 3 records per customer.

Any help would be greatly appreciated.

DW

Re: Selecting limited observartions for each customer

Olivier — Fri, 13 Oct 2006 16:03:08 GMT

I'm afraid this can't be done without opening a Code window and typing programming statements.
For example :

PROC SORT DATA = yourDataSet OUT = work.sortedData ;
BY customerId
DESCENDING dateVariable ;
RUN ;
DATA yourFinalDataSet ;
SET work.sortedData ;
BY customerId ;
IF FIRST.customerId THEN nRows = 0 ; /* for a new customer, set nRows to 0 */
nRows + 1 ; /* increment nRows for each observation */
IF nRows <= 3 THEN OUTPUT ;
/* only keep records when nRows < 4 */
RUN ;