Date:         Wed, 13 May 2009 14:16:20 -0700
Reply-To:     "Choate, Paul@DDS" <pchoate@DDS.CA.GOV>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Choate, Paul@DDS" <pchoate@DDS.CA.GOV>
Subject:      Re: Need help with data manipulation
Comments: To: mailxinli@GMAIL.COM
In-Reply-To:  A<a17a2c92-6e55-444f-b1af-0c151f1c8167@r34g2000vba.googlegroups.com>
Content-Type: text/plain; charset="us-ascii"

Xin Li - for a data step solution try this:

data counts;
     set counts;
     by customernbr loan_code;
     if first.customernbr then loan_count_group=0;
     if first.loan_code then loan_count_group+1;
run;


This is from your example:

data counts;
input customernbr loan_code loan_counts;
cards;
100  1  1
100  1  0
100  1  0
100  1  0
100  2  1
100  2  0
100  2  0
100  3  1
101  4  1
101  4  0
101  4  0
101  5  1
101  5  0
101  5  0
;

/* sort the data if needed */
proc sort;
     by customernbr loan_code;
run;

data counts;
     set counts;
     by customernbr loan_code;
     if first.customernbr then loan_count_group=0;
     if first.loan_code then loan_count_group+1;
run;


hope that helps -

Paul Choate
DDS Data Extraction
(916) 654-2160

-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of
mailxinli@GMAIL.COM
Sent: Wednesday, May 13, 2009 1:42 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Need help with data manipulation

I have data as following

customernbr   loan_code   loan_counts

      100  1  1
      100  1  0
      100  1  0
      100  1  0
      100  2  1
      100  2  0
      100  2  0
      100  3  1
      101  4  1
      101  4  0
      101  4  0
      101  5  1
      101  5  0
      101  5  0

I need to generate a new variable loan_count_group based on the
customernbr and loan_code. For the same customernbr, accumulate the
loan_counts of different loan_code, like following:

customernbr   loan_code   loan_counts  loan_count_group
      100  1  1   1
      100  1  0   1
      100  1  0   1
      100  1  0   1
      100  2  1   2
      100  2  0   2
      100  2  0   2
      100  3  1   3
      101  4  1   1
      101  4  0   1
      101  4  0   1
      101  5  1   2
      101  5  0   2
      101  5  0   2

your help will be much appreciated!

-Xin Li