Columns values within column

Siddhartha — Fri, 10 Dec 2010 15:28:40 GMT

data outst_amt;
input prd_cat : $25. cus_cat & $20. Date_values & $20. Out_amount ;
datalines;
Hire-Purchase Business-banking 25thaug2010 234
Housing-Loans Consumer-banking 26-Aug-2010 243
Credit-card Business-banking 26-Aug-2010 342
Personal-Loan Consumer-banking 25-Aug-2010 456
Co-op-Personal-Loan Business-banking 27-Aug-2010 356
Share-Margin-Financing Consumer-banking 28-Aug-2010 123
Revolving-Credits Business-banking 25-Aug-2010 213
Syndicated-Loans Consumer-banking 24-Aug-2010 432
Trade-Financing Business-banking 29-Aug-2010 124
Term-Loans Consumer-banking 31-Aug-2010 145
Overdraft Business-banking 25-Aug-2010 234
Other-loans Consumer-banking 26-Aug-2010 187
;
run;

I want the output as follows:

Item Descriptions ------Date values------- 25-Aug 26-Aug 27-aug 28-Aug
A.Loans by Business Segment
Business banking 314 345
Consumer banking 234 213
Total Gross Loans

B.Gross Loans by Products
Hire Purchase 367 276
Housing Loans 342
Credit card
Personal Loan 243
Co-op Personal Loan
Share Margin Financing 234 567
Revolving Credits 546
Syndicated Loans
Trade Financing
Term Loans
Overdraft 345 657 546
Other loans
Total Gross Loans

Can anyone help me on this, as I was strucked from morning onwards.
Thanks in advance

Re: Columns values within column

Cynthia_sas — Fri, 10 Dec 2010 16:36:53 GMT

Hi:
Again, I see that you have posted this same question in the SAS Procedures forum. http://support.sas.com/forums/message.jspa?messageID=47222#47222

If this question is about reporting (and it seems that it is), then it would be easier for everyone to only track the question(s) and answers in one forum. Pick a forum for your question, one forum is best.

It seems that you want some report output (as opposed to dataset output) so you might want to just leave this question in the ODS and Reporting forum.

Is this question at all related to your two previous questions:
http://support.sas.com/forums/message.jspa?messageID=47176#47176
http://support.sas.com/forums/message.jspa?messageID=47143#47143

The only difference that I see between this output and some of your previous output is that now, your desired output shows the dates going along the top of the report -- this is the type of report that you might be able to generate with PROC TABULATE.

However, I don't see the "A. Loans by Busienss Segment" and "B. Gross Loans by Product" in your data -- so to me this seems like another reason to use PROC REPORT with a COMPUTE BLOCK and a LINE statement to write customized headers. A simple modification of one of the programs that's already been posted in your other forum would allow you to write a custom header before a group. Or, you could use PROC TABULATE with custom BOX labels to put these "extra" report headers. Since I already posted a PROC REPORT program for you, I am posting a TABULATE program to help you get started.

This actually looks like 2 different summaries...the top of your desired report looks like a summary of the CUS_CAT variable and the bottom half of your desired reports looks like a summary of the PRD_CAT variable.

Perhaps it is time to take a step back and learn a bit more about SAS reporting procedures like PROC REPORT and PROC TABULATE in order for you to figure out which one of these would give you the desired result. If you search the forums, you will find a lot of previous postings with links to user-group papers about the use of PROC REPORT and PROC TABULATE. Perhaps those will get you started.

cynthia
[pre]
data outst_amt;
length prd_cat $25 cus_cat $20 ;
infile datalines dsd dlm=',';
input prd_cat $ cus_cat $ Date_values : anydtdte. Out_amount ;
return;
datalines;
"Hire Purchase","Business banking","25-aug-2010",234
"Housing Loans","Consumer banking","26-aug-2010",243
"Credit card","Business banking","26-aug-2010",342
"Personal Loan","Consumer banking","25-aug-2010",456
"Co-op Personal Loan","Business banking","27-aug-2010",356
"Share Margin Financing","Consumer banking","28-aug-2010",123
"Revolving Credits","Business banking","25-aug-2010",213
"Syndicated Loans","Consumer banking","24-aug-2010",432
"Trade Financing","Business banking","29-aug-2010",124
"Term Loans","Consumer banking","31-aug-2010",145
"Overdraft","Business banking","25-aug-2010",234
"Other loans","Consumer banking","26-aug-2010",187
;
run;

ods listing;
proc print data=outst_amt;
title 'what does data look like';
format date_values date6.;
run;

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

options missing = 0;
proc tabulate data=outst_amt;
class date_values / order=internal;
class prd_cat cus_cat / order=data;
var out_amount;
table cus_cat all='Total Gross Loans',
date_values*out_amount=' '*sum /
box='A. Loans by Business Segment';

table prd_cat all='Total Gross Loans',
date_values*out_amount=' '*sum /
box='B. Gross Loans by Product';

format date_values date6.;
label prd_cat='Item Descriptions'
cus_cat='Item Descriptions'
date_values='Date Values';
keylabel sum=' ';
run;
ods html close;
[/pre]

topic Columns values within column in ODS and Base Reporting

Columns values within column

Re: Columns values within column