topic Re: Column group,subgroup,sub-subgroup grouping in SAS Procedures

Column group,subgroup,sub-subgroup grouping

Siddhartha — Thu, 09 Dec 2010 18:02:32 GMT

I have four columns named prd_grp,prd-subgroup,prd_desc and loan_amount.
The data is as follows:

prd_grp prd_subgroup prd_desc loan_amount

ASSETS All_Other Loans 234
ASSETS All_Other abc 345
ASSETS All_Other Quoted_Shares 213
LIABILIITES Deposits Current_Deposs 435
LIABILIITES Deposits Fixed 325
LIABILIITES Deposits NIDs & Repos 256
LIABILIITES Deposits Savings 218

Below is the output I needed

Item Loan_amount

ASSETS
All_Other
Loans 234
abc 345
Quoted_Shares Held 213
Total 692
LIABILIITES
Deposits
Current Deposits 435
Fixed 325
NIDs & Repos 256
Savings 218
Grand_Total 1234

Can anyone help me on this, as this is critical.
Thanks in advance.

Regards,
Siddhartha

Re: Column group,subgroup,sub-subgroup grouping

ArtC — Thu, 09 Dec 2010 18:57:35 GMT

You did not specify how you wanted your results (data or report), so here are a couple of possible solutions.
[pre]data balance;
length prd_grp $11 prd_subgroup $10 prd_desc $15;
input prd_grp $ prd_subgroup $ prd_desc $ loan_amount;
datalines;
ASSETS All_Other Loans 234
ASSETS All_Other abc 345
ASSETS All_Other Quoted_Shares 213
LIABILIITES Deposits Current_Deposs 435
LIABILIITES Deposits Fixed 325
LIABILIITES Deposits NIDs&Repos 256
LIABILIITES Deposits Savings 218
run;
title 'balance sheet';
proc summary data=balance ;
class prd_grp prd_desc ;
var loan_amount;
output out=sumry(where=(_type_ ne 1)) sum=total;
run;
proc print data=sumry;
run;

proc report data=balance nowd;
column prd_grp prd_desc loan_amount;
define prd_grp / group;
define prd_desc / group;
define loan_amount / analysis sum;
break after prd_grp / summarize;
rbreak after / summarize;
run;[/pre]
Your example output did not really show what you wanted to do with prd_subgroup, but adding it into one of these solutions is very straightforward.

Re: Column group,subgroup,sub-subgroup grouping

SPR — Thu, 09 Dec 2010 19:42:54 GMT

Hello Suddhartha,

This is another solution (pure dataset operations):
[pre]
data i;
length prd_grp prd_subgroup prd_desc $15;
input prd_grp $ prd_subgroup $ prd_desc $ loan_amount;
datalines;
ASSETS All_Other Loans 234
ASSETS All_Other abc 345
ASSETS All_Other Quoted_Shares 213
LIABILIITES Deposits Current_Deposs 435
LIABILIITES Deposits Fixed 325
LIABILIITES Deposits NIDs_&_Repos 256
LIABILIITES Deposits Savings 218
;
run;
data t;
retain prd_grp prd_subgroup prd_desc loan_amount total grand_total pr;
set i end=ie;
if _n_=1 then grand_total=0;
if first.prd_grp then do; pr=1; output; end;
if first.prd_subgroup then do; pr=2; output; total=0; end;
pr=3; output;
total+loan_amount;
if last.prd_subgroup then do; pr=4; output; end;
grand_total+loan_amount;
by prd_grp prd_subgroup;
keep prd_grp prd_subgroup prd_desc loan_amount total grand_total pr;
if ie then do; pr=5; output; end;
run;
data r(keep=Item Loan_Amount);
retain Item Loan_Amount;
set t;
if pr=1 then do; Item=prd_grp; loan_amount=.; end;
if pr=2 then do; Item=prd_subgroup; loan_amount=.; end;
if pr=3 then do; Item=prd_desc; end;
if pr=4 then do; Item="Total"; loan_amount=Total; end;
if pr=5 then do; Item="Grand Total"; loan_amount=grand_Total; end;
run;
[/pre]
Sincerely,
SPR

Re: Column group,subgroup,sub-subgroup grouping

Cynthia_sas — Thu, 09 Dec 2010 20:45:02 GMT

Hi:
You already posted this question in the ODS forum and several answers have been posted there, too.

cynthia