Transposing data within Proc Report

[christyh]

Hi there,

I have a dataset that has a bunch of flag variables that I want to count up so I can calculate percentages, etc. I can do tons of data manipulation to get what I want (including proc freq, transpose, etc.) but I thought I might be able to do this in Proc Report.

Here's my code:
proc report data=test nowd;
where rb=1;
column rb deposits home_lending credit_card;
define rb / sum "Total Retail Bank Customers";
define deposits / sum "Total Deposit Products";
define home_lending / sum "Total Home Lending Products";
define credit_card / sum "Total Credit Card Deposits";
run;

This gets me the results that I want/need for my calculations but it lists the variables across the top of the report. I would really like to have the product lists go down with all the stats headings across the top. Does that make sense?

Thanks for your help.
Christy

[Cynthia_sas]

Hi:
I see that you have all "analysis" variables (usage of SUM) in the PROC REPORT sample code. Generally, in order for products to go "down" the rows, you would need to have an order or group variable in the COLUMN statement and with a usage of ORDER or GROUP in a DEFINE statement.

Consider, for example, the data set SASHELP.CLASS. If you run the code below, you will see that example 1, with only analysis variables only produces a 1 line report. When the SEX variable is added to the COLUMN statement and used as a GROUP variable, then the report becomes a 2 line report -- with a report row for F and a report row for M.

Extending this concept, report #3 shows getting some extra/new statistics, such as a mean for AGE and N and PCTN. I would have expected to see your variable for PRODUCT LIST on the report in order for you to get a report row for every product, for example.

PROC REPORT also has the ability to create cross-tabular reports with variables having a definition of ACROSS usage (as shown in report #4 -- which has some statistics nested underneath across variables).

cynthia
[pre]
ods listing close;
ods html file='c:\temp\rept_examp.html' style=sasweb;

proc report data=sashelp.class nowd;
title '1) Only Analysis variables';
column age height weight;
define age / sum;
define height / sum;
define weight / sum;
run;

proc report data=sashelp.class nowd;
title '2) With GROUP usage item';
column sex age height weight;
define sex / group 'Gender';
define age / sum;
define height / sum;
define weight / sum;
run;

proc report data=sashelp.class nowd;
title '3) With Statistics and GROUP usage item';
column sex age age=agen age=agep height weight;
define sex / group 'Gender';
define age / mean "Avg Age";
define agen / n 'Count';
define agep / pctn 'PctN';
define height / sum;
define weight / sum;
run;

proc report data=sashelp.class nowd;
title '4) With ACROSS Usage item';
column age sex,(n pctn height);
define age / group 'Age';
define sex / across "Gender";
define height / mean 'Avg Ht' f=8.2;
define n / 'Count';
define pctn / 'PctN' f=percent9.2;
run;
ods html close;

[/pre]