BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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.
4 REPLIES 4
Cynthia_sas
SAS Super FREQ
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
Not applicable
Thank you very much Cynthia. You always help me 😉
deleted_user
Not applicable
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
SAS Super FREQ
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 671 views
  • 0 likes
  • 2 in conversation