Date:         Mon, 9 Apr 2012 20:08:41 +0000
Reply-To:     "Zdeb, Michael S" <mzdeb@ALBANY.EDU>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Zdeb, Michael S" <mzdeb@ALBANY.EDU>
Subject:      Re: SQL group by and having sum
Comments: To: Mirisage Fernando <neilfrnnd@GMAIL.COM>
In-Reply-To:  <201204091932.q39HM0em013829@waikiki.cc.uga.edu>
Content-Type: text/plain; charset="us-ascii"

hi ...

proc summary data=a nway;
where product in ('Personal OD','Personal Loan','Res. Mortgage', 'Comm. Mortgage');
class current_date bank_number;
var balance;
output out=stats (drop=_:) sum=;
run;

Mike Zdeb
U@Albany School of Public Health
One University Place (Room 119)
Rensselaer, New York 12144-3456
P/518-402-6479 F/630-604-1475

________________________________________
From: SAS(r) Discussion [SAS-L@LISTSERV.UGA.EDU] on behalf of Mirisage Fernando [neilfrnnd@GMAIL.COM]
Sent: Monday, April 09, 2012 3:32 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: SQL group by and having sum

Hi Collegues;

I have the following data set (1.3 million). Note entries of the
variable 'Product' are having spaces and dots within.

data a;
Informat current_date date9.;
input Current_date Acct_no country $ Balance  Arrears_Band $ 29-36 Product
$ 38-51 Bank_number;
Format current_date date9.;
datalines;
31JUL2010  1111 Africa    .  current Personal OD    40
31JUL2010  2222 Africa   25 current  Personal OD    10
31JUL2010  0000 Africa   75 1 - 30   Corporate OD   70
31JUL2010  8888 Africa   75 30 - 60  Res. Mortgage  20
31JUL2010  3333 Asia     20 current  Res. Mortgage  30
31JUL2010  4444 Europe  500 current  Corporate Loan 10
31JUL2010  5555 Aus     100 current  OTHER          20
31JUL2010  6666 Aus     800 current  Corporate OD   70
31AUG2010  1111 Africa  100 current  Personal OD    40
31AUG2010  2222 Africa   10 1 - 30   Personal OD    30
31AUG2010  0000 Africa   25 writeoff Personal OD    30
31AUG2010  8888 Africa   25 NPNA     Personal OD    70
31AUG2010  3333 Asia     20 default  Corporate Loan 40
31AUG2010  4444 Europe  500 current  Corporate Loan 30
31AUG2010  5555 Aus     100 current  OTHER          40
31AUG2010  6666 America 800 current  Comm. Mortgage 70
;
run;

I need to get the sum of variable 'balance' grouped by Current_date and
Bank_number variables but only for these products:

'Personal OD','Personal Loan','Res. Mortgage' and 'Comm. Mortgage

I have created the following code with your support but it does not group
accurately.

proc sql;
create table LOSS as
select
Current_date      ,
country           ,
sum(case when Arrears_Band IN ('current') then balance else 0
end )           as current,
sum(case when Arrears_Band IN ('1 - 30') then balance else 0
end )           as One_to_Thirty,
sum(case when Arrears_Band IN ('30 - 60') then balance else 0
end )           as Thirty_to_Sixty,
sum(case when Arrears_Band IN ('60 - 90') then balance else 0
end )           as Sixty_to_Ninety,
sum(case when Arrears_Band IN ('90 +') then balance else 0
end)               as Ninety_plus,
sum(case when Arrears_Band IN ('NPNA') then balance else 0
end)               as NPNA,
sum(case when Arrears_Band IN ('default') then balance else 0
end)               as default,
sum(case when Arrears_Band IN ('writeoff') then balance else 0
end)               as writeoff

from a
group by Current_date, Bank_number
having  Product IN ('Personal OD','Personal Loan','Res. Mortgage','Comm.
Mortgage');
;
quit;

I highly appreciate your help.

Mirisage