DATA Step, Macro, Functions and more

average sales in an year

Reply
Super Contributor
Posts: 647

average sales in an year

outlet product segment mon1 to mon12 ,where mon1 to mon12 are sales dollars for 12 months,are the fields in the dataset.there are 7000 records.
I'm wondering if we can find the average of sales for year without using proc transpose to bring in all mon1 to mon12 under single column?
Super Contributor
Posts: 291

Re: average sales in an year

total=sum(of mon1-mon12);
Valued Guide
Posts: 632

Re: average sales in an year

> total=sum(of mon1-mon12);
avesales = mean(of mon1-mon12);

There are a number of statistics functions. These roughly correspond to the statistics available in the MEANS/SUMMARY procedure. Functions work against the PDV (across variables) for an observation. The MEANS/SUMMARY works across observations.
Super Contributor
Posts: 647

Re: average sales in an year

This gives avg sales per record.but I want avg sales for whole data.
SAS Super FREQ
Posts: 8,743

Re: average sales in an year

Hi:
Your original post said that you wanted
average of sales for year . I made the same assumption that other folks did and I thought your data probably looked something like this:
[pre]
Year mon1 mon2 mon3 mon4 mon5 mon6 mon7 mon8 mon9 mon10 mon11 mon12
2000 264074 269994 265954 266141 280331 266302 272390 273274 285721 286556 278729 279212
2001 278466 278948 286522 278980 278516 275090 285408 283625 274469 287157 288108 281379
2002 330275 337671 332619 332849 350591 333036 340659 341773 357322 358370 348601 349192
2003 348255 348874 358334 348904 348321 344049 356928 354707 343262 359132 360316 351914
2004 348255 348874 358334 348904 348321 344049 356928 354707 343262 359132 360316 351914
2005 264074 269994 265954 266141 280331 266302 272390 273274 285721 286556 278729 279212
2006 278466 278948 286522 278980 278516 275090 285408 283625 274469 287157 288108 281379
2007 330275 337671 332619 332849 350591 333036 340659 341773 357322 358370 348601 349192
[/pre]

You said you had 7000 obs, I just figured you had a yearly data for some group scenario. Now, I don't understand what you mean by "avg sales for whole data" -- average sales for ALL the MONTHS across ALL the obs?????

I don't see that you'd need to transpose the data to get an overall mean or a grand total. If you did have a sum across all the months for every obs, then either PROC MEANS, PROC TABULATE or PROC REPORT could get you either a report or an output dataset. Only the reports are shown in the code below. Note that PROC TABULATE can produce multiple tables with different combos of month, year variables.

cynthia
[pre]
data yrdata;
infile datalines;
input year mon1-mon12;
return;
datalines;
2000 264074 269994 265954 266141 280331 266302 272390 273274 285721 286556 278729 279212
2001 278466 278948 286522 278980 278516 275090 285408 283625 274469 287157 288108 281379
2002 330275 337671 332619 332849 350591 333036 340659 341773 357322 358370 348601 349192
2003 348255 348874 358334 348904 348321 344049 356928 354707 343262 359132 360316 351914
2004 348255 348874 358334 348904 348321 344049 356928 354707 343262 359132 360316 351914
2005 264074 269994 265954 266141 280331 266302 272390 273274 285721 286556 278729 279212
2006 278466 278948 286522 278980 278516 275090 285408 283625 274469 287157 288108 281379
2007 330275 337671 332619 332849 350591 333036 340659 341773 357322 358370 348601 349192

;
run;

title;
data yrtot;
set yrdata;
obstot = sum(of mon1-mon12);
run;

** Using PROC MEANS for OVERALL SUM and MEAN;
proc means data=yrtot sum mean;
var obstot;
run;

** Using PROC REPORT for a REPORT with the overall SUM and MEAN and individual months too;
proc report data=yrtot nowd;
column year mon1-mon12 obstot obstot=mntot;
define year / group;
define obstot / sum 'Sum of All Months' f=comma12.;
define mntot / mean 'Mean of All Months' f=comma12.;
rbreak after/summarize;
format mon1-mon12 comma12.;
run;

** Using PROC TABULATE for a REPORT with the overall SUM and MEAN;
proc tabulate data=yrtot f=comma12.;
var mon1-mon12 obstot;
class year;
table year all,
sum*(mon1-mon12) obstot*(sum mean) / box='With Year';

table year all,
obstot*(sum mean) / box='With Year but Without Months';

table all,
sum*(mon1-mon12) obstot*(sum mean) / box='Without Year';

table all,
obstot*(sum mean) / box='Without Months';
run;
[/pre]
Ask a Question
Discussion stats
  • 4 replies
  • 151 views
  • 0 likes
  • 4 in conversation