Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Home
- /
- Programming
- /
- SAS Procedures
- /
- Re: Column group,subgroup,sub-subgroup grouping

Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Posted 12-09-2010 01:02 PM
(1043 views)

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

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

3 REPLIES 3

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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.

[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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Hi:

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

cynthia

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

cynthia

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. **Registration is now open through August 30th**. Visit the SAS Hackathon homepage.

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.