DATA Step, Macro, Functions and more

Build Crosstab table using Array

Reply
Occasional Contributor
Posts: 19

Build Crosstab table using Array

Hi, I am new to SAS and have been lurking on this forum for a while and have learned a lot thanks. I have a question of my own now that I was wondering if someone could help me out. I have a data set that has three columns Weeks, Class and Sales. It looks like this:

Week Class SALES
week1 A 5.99
week1 B 3.45
week2 C 5.88
week3 B 4.56
...

I want to build a table that has weeks going down and class going across with sales summed up by week by class it would look like this:
A B C
Week1 5.99 3.45 0
Week2 0 0 5.88
Week3 0 4.56 0

Thanks again.
Super Contributor
Super Contributor
Posts: 365

Re: Build Crosstab table using Array

Hello 1800bigk,

You need prod TRANSPOSE like this:
[pre]
proc transpose data=a out=r;
var sales;
id class;
by week;
run;
[/pre]
A result dataset R contains missing values instead of zeroes in some placces that can be easily corrected.
Sincerely,
SPR
SAS Super FREQ
Posts: 8,866

Re: Build Crosstab table using Array

Hi:
When you say you want to "build a table" I wonder whether you want a REPORT -- such as a report that you would send to HTML, RTF or PDF output or whether you want a new SAS dataset???

If all you want is a SAS dataset, then you could use PROC TRANSPOSE -- however, PROC TRANSPOSE does not automatically do a summarization of the data -- so if you had multiple observations for each week, you would not get a summary for each week. In many instances with PROC TRANSPOSE, you end up summarizing your data first and then passing the summarized data to make the PROC TRANSPOSE code easier.

However, several other procedures, notably PROC TABULATE and PROC REPORT will both produce the kind of summary table output you want -- Here is an example that shows the use of SASHELP.PRDSALE with MONTH going down the rows and COUNTRY going across the columns. In this instance, the numeric variable being summarize is the ACTUAL variable. (Both TABULATE and REPORT support the OUT= option so they could also generate a summarized dataset for you, as well as a report.)

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

proc print data=sashelp.prdsale(obs=10);
title '1) Display 10 rows of data to see what it looks like';
where year = 1994 and quarter=1;
var month country actual quarter year;
format month monyy5.;
run;

**2) Use TABULATE to get cross-tabular output;
proc tabulate data=sashelp.prdsale format=comma16.0;
where year = 1994 and quarter=1;
title '2) TABULATE example';
class month country;
var actual;
class month country;
table month all,
country*actual*sum;
keylabel sum=' '
all='Total';
format month monyy5.;
run;

**3) Use REPORT to get cross-tabular output;
proc report data=sashelp.prdsale nowd;
where year = 1994 and quarter=1;
title '3) REPORT example';
column month country,actual;
define month / group order=internal f=monyy5.;
define country / across;
define actual/ sum;
rbreak after / summarize;
run;

ods html close;
[/pre]
Occasional Contributor
Posts: 19

Re: Build Crosstab table using Array

Posted in reply to Cynthia_sas
Thank you, yes I will have multiple observations by week for each class. Thanks again.
Ask a Question
Discussion stats
  • 3 replies
  • 983 views
  • 0 likes
  • 3 in conversation