BookmarkSubscribeRSS Feed
1800bigk
Fluorite | Level 6
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.
3 REPLIES 3
SPR
Quartz | Level 8 SPR
Quartz | Level 8
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
Cynthia_sas
SAS Super FREQ
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]
1800bigk
Fluorite | Level 6
Thank you, yes I will have multiple observations by week for each class. Thanks again.

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 3 replies
  • 2089 views
  • 2 likes
  • 3 in conversation