Proc Tabulate limitation of output

[Lost_Gary]

I have this code:

data stock2;

set sashelp.stocks;

year = year(date);

run;

proc tabulate data=stock2;

var Close;

class year Stock;

table year all,stock*close=' '*mean;

run;

I believe my output is average stock price per year, with an overall average for the entire dataset.  Is there a way to reduce this output (say everything since the year 2000), but maintain the average stock price from the entire data set?  Like an "if" statement (if year <2000 then delete)?  Or something that limits the display of the output to just those months - but maintains the calculation for the entire data?  

[ballardw]

Presummarize using Proc summary and then play some games with _type_ of the resulting data set.

[RichardDeVen · Accepted solution]

You can do this using a custom multilabel format.  'All' is somewhat misleading to a typical consumer of such reports, so you should modify the label or have a disclaimer.

Example:

ods html file='stocks.html' style=plateau;

proc format;
  value mlyear(multilabel)
    '01jan2000'd-'31dec2005'd = [year.]
    low-high = 'All'
  ;
run;

proc tabulate data=sashelp.stocks;
title 'Various stocks averages prices';
var Close;
class date stock;
table date all,stock*close=' '*mean;
format date year.;
run;

proc tabulate data=sashelp.stocks;
title2 'All includes data from years not shown';
var Close;
class date / mlf;
class stock;
table date,stock*close=' '*mean;
format date mlyear.;
run;

ods html close;

Output

[Lost_Gary]

I actually used this formatting for my data to report he previous 3 months of data.  It worked great.  Until I tried to report the information for October.  And it isn't wrong (at least as far as I know), but it now is out of order.  So I have the display of:

August

October

September

YTD

Nothing I do seems to be able to change this order.  So frustrating.  Any thoughts on how to adjust this?  

[RichardDeVen]

The CLASS option / MLF ORDER=DATA PRELOADFMT is used to force a specific ordering of format value when the custom format is built with the NOTSORTED option.  However, those mix of options won't work because the formatted value is a map from value range to another format

proc format;
  value mlyear(multilabel notsorted)
    '01jan2000'd-'31dec2005'd = [yymon.]   /* value range to other format */
    low-high = 'All'
  ;
run;

You can force a specific order that works with NOTSORTED by enumerating every date in a CNTLIN data set.

data cntlin;
  retain
    fmtname 'monthsbyday'
    hlo 'MS'  /* options multilabel and notsorted */
  ;
  do start = '01jan2000'd to '31dec2025'd;
    label = put(start,monname.);  /* map date to month name */
    output;
  end;
  do start = '01jan2000'd to '31dec2025'd;
    label = 'YTD ' || put(start,year4.); /* map date to YTD <yyyy> */
    output;
  end;
run;

proc format cntlin=cntlin;
run;

You can use the format for data from a single year

ods html file='stocks.html' style=plateau;

proc tabulate data=sashelp.stocks;
title2 'All includes data from years not shown';
var Close;
class date / mlf order=data preloadfmt;
class stock;
table date,stock*close=' '*mean;
format date monthsbyday.;
where year(date) = (2004);
run;

ods html close;

Output

If the data being reported spans more than one year you will need an additional class variable YEAR and code

table year*date,stock*close=' '*mean;

Without the additional year class, the months will aggregate from two years instead of one.