Help using Base SAS procedures

Transpose 1 column in 3 column table

Reply
N/A
Posts: 0

Transpose 1 column in 3 column table

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.
SAS Super FREQ
Posts: 8,868

Re: Transpose 1 column in 3 column table

Posted in reply to deleted_user
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]
N/A
Posts: 0

Re: Transpose 1 column in 3 column table

Posted in reply to Cynthia_sas
Thank you very much Cynthia. You always help me Smiley Wink
N/A
Posts: 0

Re: Transpose 1 column in 3 column table

Posted in reply to Cynthia_sas
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
SAS Super FREQ
Posts: 8,868

Re: Transpose 1 column in 3 column table

Posted in reply to deleted_user
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
Ask a Question
Discussion stats
  • 4 replies
  • 128 views
  • 0 likes
  • 2 in conversation