topic Re: Transpose 1 column in 3 column table in SAS Procedures

Transpose 1 column in 3 column table

deleted_user — Thu, 30 Oct 2008 18:11:26 GMT

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.

Re: Transpose 1 column in 3 column table

Cynthia_sas — Thu, 30 Oct 2008 21:22:15 GMT

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]

Re: Transpose 1 column in 3 column table

deleted_user — Mon, 03 Nov 2008 10:43:07 GMT

Thank you very much Cynthia. You always help me 😉

Re: Transpose 1 column in 3 column table

deleted_user — Mon, 03 Nov 2008 14:24:41 GMT

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

Re: Transpose 1 column in 3 column table

Cynthia_sas — Mon, 03 Nov 2008 18:26:24 GMT

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