Date:         Wed, 8 Jul 2009 13:46:16 -0700
Reply-To:     "Terjeson, Mark" <Mterjeson@RUSSELL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Terjeson, Mark" <Mterjeson@RUSSELL.COM>
Subject:      Re: Need help with data manipulation
Comments: To: Xin <mailxinli@GMAIL.COM>
In-Reply-To:  A<8c951108-7abe-4104-8e17-a0d6b5f11562@s31g2000yqs.googlegroups.com>
Content-Type: text/plain; charset="us-ascii"

Hi Xin,

The FIRST.CustomerNbr sets a flag to 1
when it is the first record in the group,
thus we can reset a simple counter that
is RETAINed across observations.  e.g.


data sample;
    input CustomerNbr Loan_code;
cards;
100 777
100 778
100 779
100 780
200 773
200 774
200 775
300 899
300 890
300 891
300 892
;
run;

    * use of FIRST. LAST. requires sorting ;
proc sort data=sample out=temp1;
    by CustomerNbr Loan_code;
run;

    * use FIRST. to reset counter ;
data result;
    set temp1;
    by CustomerNbr Loan_code;
    retain customer_n_th_loan .;
    if first.CustomerNbr then customer_n_th_loan=0;
    customer_n_th_loan+1;
run;




-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Xin
Sent: Wednesday, July 08, 2009 1:40 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Need help with data manipulation

I have data as following:

CustomerNbr     Loan_ code
 100                     777
 100                     778
 100                     779
 100                     780

 200                     773
 200                     774
 200                     775

 300                     899
 300                     890
 300                     891
 300                     892

And then, I need to generate a variable to represent n- th loans taken
by each customer like following

CustomerNbr     Loan_ code     customer_n_th_loan
 100                     777                        1
 100                     778                        2
 100                     779                        3
 100                     780                        4

 200                     773                        1
 200                     774                        2
 200                     775                        3

 300                     899                        1
 300                     890                        2
 300                     891                        3
 300                     892                        4


Your help will be much appreciated!

- Xin