Transpose 1 column in 3 column table

[deleted_user]

Hi guys

Please help me to solve a problem, regarding proc transpose.
For example I have a table:
-------------------------------------------------------------------
| CUSTOMER | DEPARTMENT | PRODUCT |
-------------------------------------------------------------------
| 001 | 300 | Coffe |
| 002 | 500 | Tea |
| 002 | 500 | Tea |
| 002 | 500 | Coffe |
| 003 | 700 | Coffe |
| 003 | 700 | Tea |

And I need to write it to excel file in the following view:
-------------------------------------------------------------------
| CUSTOMER | DEPARTMENT | Coffe | Tea |
-------------------------------------------------------------------
| 001 | 300 | 1 | 0 |
| 002 | 500 | 1 | 2 |
| 003 | 700 | 1 | 1 |

I tried to use PROC TRANSPOSE here, but cannot understand how does it work.
Please help.
Thank you.

[Cynthia_sas]

Hi:
You want PROC TRANSPOSE to summarize according to customer and department, but it's just not going to cooperate. This is really a job for one of the summary report procedures, like PROC REPORT or PROC TABULATE.

I'd probably pick REPORT, but that's because in the grand scheme of things, I'm probably more of a REPORT person than a TABULATE person.

To get the output into Excel, I'd choose either HTML file or a Spreadsheet Markup Language file, as shown in the code below. Although, you could make an output dataset from either REPORT or TAB and then use PROC EXPORT, if you wanted to.

cynthia
[pre]
data product;
infile datalines;
input customer $ department product $;
return;
datalines;
001 300 Coffee
002 500 Tea
002 500 Tea
002 500 Coffee
003 700 Coffee
003 700 Tea
;
run;

title; footnote;
ods listing close;
options missing=0;

ods msoffice2k file='c:\temp\prod_HT.xls' style=sasweb;
ods tagsets.excelxp file='c:\temp\prod_XP.xls' style=sasweb;

proc report data=product nowd;
column ('With Proc Report' customer department)
n,product ;
define customer / group ;
define department / group;
define product /across;
define n / ' ';
run;

proc tabulate data=product f=comma6.;
class customer department product;
table customer*department,
n*product /box='With Proc Tabulate';
keylabel n=' ';
run;

ods _all_ close;


[/pre]

[deleted_user]

Thank you very much Cynthia. You always help me 😉

[deleted_user]

Hi Cynthia
I am going to ask you about help again.
Can you please explain how to rebuild this proc report to have data in following view:
----------------------------------------------------------------------------
PRODUCT | DEPARTMENT | CUSTOMER_COUNT |
----------------------------------------------------------------------------
Coffe | 300 | 3
Tea | 500 | 2

[Cynthia_sas]

Hi:
In this instance, you want to count or summarize the customers for each product, but within each product, you want to show the counts for each department. In Proc Report, this kind of summarizing is accomplished by defining a variable or variable as a GROUP variable. Since all you want is the COUNT, you can accomplish this by asking for the N statistic on the column statement:
[pre]
proc report data=product nowd
split='#';
title 'Proc Report Counts for Product and Department';
column product department n;
define product /group 'Prod';
define department / group 'Dept';
define n / 'Customer#Count';
run;
[/pre]

Or, you could ask for the explicit count of customers with this version of the code, but for all intents and purposes, the count of all observations( example above) and the count of customers (example below) will get you the same results:
[pre]
proc report data=product nowd;
title 'Proc Report Cross Customer with Count';
column product department customer,n;
define product /group 'Prod';
define department / group 'Dept';
define n / 'Count';
define customer / 'Customer';
run;
[/pre]

cynthia