Desktop productivity for business analysts and programmers

Selecting limited observartions for each customer

Reply
N/A
Posts: 0

Selecting limited observartions for each customer

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
Super Contributor
Posts: 260

Re: Selecting limited observartions for each customer

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 ;
Ask a Question
Discussion stats
  • 1 reply
  • 103 views
  • 0 likes
  • 2 in conversation