Date:         Thu, 13 Oct 2011 17:34:29 +0800
Reply-To:     "Dai, Randy" <Randy.Dai@ASTRAZENECA.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Dai, Randy" <Randy.Dai@ASTRAZENECA.COM>
Subject:      Re: Select the last 6 records of each ID
In-Reply-To:  A<201110130737.p9D4JKSB031339@waikiki.cc.uga.edu>
Content-Type: text/plain; charset="iso-2022-jp"

data test;
infile cards ;
length Client_No $ 9;
input obs  Client_No $ transaction_date anydtdte. ;
format transaction_date YYMMDD.;
cards;
 1 A1015XXXX 2005/2/2
 2 A1015XXXX 2005/1/1
 3 A1015XXXX 2004/12/12
 4 A1015XXXX 2004/11/11
 5 A1015XXXX 2004/10/10
 6 A1015XXXX 2004/9/9
 7 A1015XXXX 2004/8/8
 8 A1015XXXX 2004/7/7
 9 A1031XXXX 2004/8/8
10 A1102XXXX 2002/10/10
11 A1202XXXX 2007/12/6
12 A1202XXXX 2007/11/6
13 A1202XXXX 2007/10/6
14 A1202XXXX 2007/9/6
15 A1202XXXX 2007/8/6
16 A1202XXXX 2007/7/2
17 A1202XXXX 2007/6/12
18 A1202XXXX 2007/5/15
;
run;

proc sort data=test(drop=obs)  out=test2 ;
by Client_No transaction_date ;
run;

data test3;
set test2;
by Client_No transaction_date;
retain count; count+1;
if first.Client_No and first.transaction_date then count=1;
run;

proc sql;
create table clientmax as
select Client_No, max(count) as maxcount from test3
group by Client_No;
quit;

proc sql;
create table test4 as
select  (maxcount-count) as lastdiff, * from test3 as A left join Clientmax as B
on A.Client_No=B.Client_No;
quit;

data final;
set test4;
where lastdiff<5;
drop lastdiff  count maxcount;
run;

-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Stanley Luo
Sent: 2011 $BG/ (J10 $B7n (J13 $BF| (J 15:38
To: SAS-L@LISTSERV.UGA.EDU
Subject: Re: Select the last 6 records of each ID

Sorry my bad, it should be "Select the LATEST 6 records of each ID"

Regards,
Stanley

On Thu, 13 Oct 2011 03:34:01 -0400, Stanley Luo <shanminglo@GMAIL.COM>
wrote:

>Dear SAS-Ls:
>The question is...
>I want to select the last 6 transaction records of the same client.
>Though some clients may have less than 6 records.
>
>For example below:
>
>obs  Client_No  transaction_date
> 1 A1015XXXX 2005/2/2
> 2 A1015XXXX 2005/1/1
> 3 A1015XXXX 2004/12/12
> 4 A1015XXXX 2004/11/11
> 5 A1015XXXX 2004/10/10
> 6 A1015XXXX 2004/9/9
> 7 A1015XXXX 2004/8/8
> 8 A1015XXXX 2004/7/7
> 9 A1031XXXX 2004/8/8
>10 A1102XXXX 2002/10/10
>11 A1202XXXX 2007/12/6
>12 A1202XXXX 2007/11/6
>13 A1202XXXX 2007/10/6
>14 A1202XXXX 2007/9/6
>15 A1202XXXX 2007/8/6
>16 A1202XXXX 2007/7/2
>17 A1202XXXX 2007/6/12
>18 A1202XXXX 2007/5/15
>
>It's kinda urgent, so any responses are highly appreciated!!
>Thanks in advance.