topic Grouping columns in ODS and Base Reporting

Grouping columns

Siddhartha — Wed, 08 Dec 2010 14:11:51 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 Held 213
LIABILIITES Deposits Current Deposits 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
Total 1234

Can anyone help me on this.
Thanks in advance.

Regards,
Siddhartha

Re: Grouping columns

Cynthia_sas — Wed, 08 Dec 2010 15:30:29 GMT

Hi:
This looks like the kind of output that you could create with PROC REPORT -- you'd put PRD_GRP and PRD_SUBGROUP on the COLUMN statement, but use the NOPRINT option on the DEFINE statement.

Then, you could put the PRD_GRP and PRD_SUBGRP into LINE statements in a COMPUTE BEFORE block in order to write the "ASSETS" or "LIABILITIES" above each group. Then you'd have an RBREAK AFTER statement to get the Total summary line at the bottom of the report.

cynthia

It would also be useful to know what your destination of interest is: ODS HTML, ODS RTF, ODS PDF????

Re: Grouping columns

Siddhartha — Thu, 09 Dec 2010 03:35:28 GMT

I want this output in HTML format

Please help me on this.

Regards,
Siddhartha

Re: Grouping columns

Cynthia_sas — Thu, 09 Dec 2010 04:35:23 GMT

Hi:
If you want ODS HTML output, you will probably use syntax like this to invoke ODS:
[pre]
ods html file='c:\temp\use_report.html' style=sasweb;
... your code ...
ods html close;
[/pre]

Have you used PROC REPORT before? If not, I recommend these user-group papers on the basics of PROC REPORT.
http://www2.sas.com/proceedings/sugi25/25/hands/25p14
http://www2.sas.com/proceedings/sugi25/25/hands/25p148.pdf

Then, learn more about the COMPUTE block:
http://www2.sas.com/proceedings/forum2008/031-2008.pdf
http://www2.sas.com/proceedings/forum2008/188-2008.pdf
http://www2.sas.com/proceedings/forum2007/242-2007.pdf
http://www2.sas.com/proceedings/forum2007/025-2007.pdf

And some other, more advanced examples:
http://www2.sas.com/proceedings/forum2007/025-2007.pdf
http://support.sas.com/resources/papers/proceedings10/133-2010.pdf

In addition, this PDF book, a version of the original PROC REPORT non-interactive mode documentation -- still contains some of the best explanations of how PROC REPORT operates.
http://support.sas.com/documentation/onlinedoc/v82/techreport_p258.pdf

Also, there are previous forum postings on COMPUTE blocks -- with code samples, such as this one:
http://support.sas.com/forums/thread.jspa?messageID=6755ᩣ

cynthia

Re: Grouping columns

Ksharp — Thu, 09 Dec 2010 08:58:00 GMT

Hi.
You must change your data's structure firstly.
This will waste some times.Good Luck.

[pre]
data op;
input prd_grp : $20. prd_subgroup & $20. prd_desc & $20. loan_amount ;
datalines;
ASSETS All Other Loans 234
ASSETS All Other abc 345
ASSETS All Other Quoted Shares Held 213
LIABILIITES Deposits Current Deposits 435
LIABILIITES Deposits Fixed 325
LIABILIITES Deposits NIDs & Repos 256
LIABILIITES Deposits Savings 218
;
run;

data op;
set op;
if prd_grp eq lag(prd_grp) and prd_subgroup eq lag(prd_subgroup)
then call missing ( prd_grp,prd_subgroup);
run;
data temp(where=(item is not missing));
set op;
item=prd_grp;output;
item=prd_subgroup;output;
item=prd_desc; _loan_amount=loan_amount;output;
keep item _loan_amount prd_subgroup;
run;
data report;
set temp;
retain id;
if not missing(prd_subgroup) then id=prd_subgroup;
drop prd_subgroup;
run;
options missing=' ';
ods html file='c:\temp\report.html' style=sasweb;
proc report data=report nowd ;
column id item _loan_amount;
define id /group noprint;
define item /display;
define _loan_amount / analysis sum;
compute after id;
item='Total';
line ' ';
endcomp;
break after id /summarize ;
run;
ods html close;
[/pre]

Ksharp

Re: Grouping columns

Cynthia_sas — Thu, 09 Dec 2010 13:05:03 GMT

Hi:
In fact, PROC REPORT -can- produce this report without restructuring the data because of the ability to use the NOPRINT options. The PRD_GRP and PRD_SUBGROUP items can be defined as GROUP or ORDER (with NOPRINT), the LINE statement will be able to use those 2 items in a COMPUTE BEFORE group.

An example, using differently named variables is shown below.

cynthia
[pre]
data repdata;
length grpvar subgrp desc $20;
infile datalines dlm="," dsd;
input grpvar $ subgrp $ desc $ amount;
return;
datalines;
"GROUP1", "SUBGRP1","One", 234
"GROUP1", "SUBGRP1","Two", 345
"GROUP1", "SUBGRP1","Three", 678
"GROUP2", "SUBGRP2","Ten", 911
"GROUP2", "SUBGRP2","Eleven", 345
"GROUP2", "SUBGRP2","Twelve", 987
"GROUP2", "SUBGRP2","Something", 432
;
run;

ods listing close;
ods html file='c:\temp\repdata.html' style=sasweb;

proc report data=repdata nowd;
column grpvar subgrp desc amount;
define grpvar / order noprint;
define subgrp / order noprint ;
define desc / order order=data 'Item';
define amount / sum 'Loan Amount';
rbreak after / summarize;
compute after;
desc = 'Total';
endcomp;
compute before subgrp /
style=Header{just=l};
line grpvar $20.;
line subgrp $20.;
endcomp;
run;
ods html close;
[/pre]

Re: Grouping columns

Ksharp — Fri, 10 Dec 2010 02:30:18 GMT

Yes.Cynthia
'line' statement is very useful.Education!

[pre]
data op;
input grpvar : $20. subgrp & $20. desc & $20. amount ;
datalines;
ASSETS All Other Loans 234
ASSETS All Other abc 345
ASSETS All Other Quoted Shares Held 213
LIABILIITES Deposits Current Deposits 435
LIABILIITES Deposits Fixed 325
LIABILIITES Deposits NIDs & Repos 256
LIABILIITES Deposits Savings 218
;
run;

ods listing close;
ods html file='c:\temp\repdata.html' style=journal;

proc report data=op nowd;
column grpvar subgrp desc amount;
define grpvar / group noprint;
define subgrp / group noprint ;
define desc / order order=data 'Item';
define amount / sum 'Loan Amount';
break after subgrp/ summarize;
compute after subgrp;
desc = 'Total';
endcomp;
compute before subgrp /
style=Header{just=l};
line grpvar $20.;
line subgrp $20.;
endcomp;
run;
ods html close;
ods listing;
[/pre]

Ksharp