topic Re: Colum values within a column in SAS Procedures

Colum values within a column

Siddhartha — Fri, 10 Dec 2010 15:31:15 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: Colum values within a column

Cynthia_sas — Fri, 10 Dec 2010 16:50:16 GMT

Hi:
This report could be done with PROC REPORT or PROC TABULATE. There is no need to post your question in more than one forum -- it makes it look like you are shopping for an answer and don't really read the descriptions of the forum topics.

You have posted this question in the ODS and Reporting forum, which is probably a more appropriate place. There has already been a PROC TABULATE example posted there.
http://support.sas.com/forums/thread.jspa?threadID=12213&tstart=0

If you have an urgent or critical need, then you should open a track with Tech Support. To open a track with Tech Support, fill out the form at this link:
http://support.sas.com/ctx/supportform/createForm

It might also be useful if you would take a step back and search for previous forum postings about PROC REPORT and PROC TABULATE to see the types of reports and types of capabilities that each procedure has. In addition, a Google Search on either of these strings:
SAS PROC REPORT beginner
SAS PROC TABULATE beginner

and you will find some useful hits that outline both REPORT and TABULATE basics.

cynthia

Re: Colum values within a column

SPR — Fri, 10 Dec 2010 18:54:13 GMT

Hello Siddhatha,

I agree with Cynthia that this task is for procs REPORT or TABULATE. Anyway the major part of the possible solution could be like this:
[pre]
proc means data=outst_amt noprint;
output out=t1 (drop=_freq_) Sum=S;
var out_amount;
class cus_cat prd_cat date_values;
run;
proc transpose data=t1 (where=(_type_=5)) out=r1 (drop=_name_) prefix=_;
ID date_values;
var S;
by cus_cat;
run;
proc transpose data=t1 (where=(_type_=3)) out=r2 (drop=_name_) prefix=_;
ID date_values;
var S;
by prd_cat;
run;
[/pre]
Sincerely,
SPR

Re: Colum values within a column

Cynthia_sas — Fri, 10 Dec 2010 22:09:25 GMT

Hi:
No TRANSPOSE is necessary, although it is a clever solution. Both PROC REPORT and PROC TABULATE will create reports directly from the data without any restructuring.

But before I post any code here, I'd like to hear from the original poster about what code they've tried and whether they've looked at or tried any of the code that's been posted in response to this and similar questions.

cynthia