Date:         Wed, 13 May 2009 15:57:27 -0500
Reply-To:     Mary <mlhoward@AVALON.NET>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Mary <mlhoward@AVALON.NET>
Subject:      Re: Need help with data manipulation
Comments: To: mailxinli@GMAIL.COM
Content-Type: text/plain; format=flowed; charset="Windows-1252";
              reply-type=original

Do you mean to have a result of 1 for 100, group 3 rather than 3?   I think
that you are looking for the counts within ID and group, in which case we
have:

proc sql noprint;
create table counts_by_group as
select customernbr, loan_code, sum(loan_counts) as count_group
from set1
group by customernbr, loan_code
order by customernbr, loan_code;
quit;

Then if you want these merged back to your original data you can do:

proc sql noprint;
create table newtable as
select  set1.customernbr,
set1. loan_code,
set1. loan_counts
counts_by_group.count_group
from set1
left outer join
counts_by_group
on set1.customernbr=counts_by_group.customernbr and
      set1.loan_code=counts_by_group.loan_code
order by set1.customernbr,  set1. loan_code;
quit;


NOT tested.


-Mary



----- Original Message -----
From: <mailxinli@GMAIL.COM>
To: <SAS-L@LISTSERV.UGA.EDU>
Sent: Wednesday, May 13, 2009 3:42 PM
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