Creating a Time Series Template with ODS

[CMC]

Hi, I need to create a time series report in excel off of a SAS dataset.  The collumns will be each of the past 13 months and the rows will contain ~30 different descriptive statistics and their values for the rolling past 13 months.  One problem I encounter are some of the descriptive statistics are in percent format, other are numeric, etc.  Can someone please send me to a location of white paper or some other source that shows how to create such a report?  Most of the ODS reporting templates I've seen on the Internet have been somewhat simplistic, with uniform character formats in the collumns, etc  Thanks for any help.

[jcbell]

I think you need to convert your numerics to character fields with the proper formats.  This is just made up data - but I think you want to do something along these lines:

data work.class;

set sashelp.class;

format height2 ratio2 $32.;

   if height>60 then height2=put (height,dollar6.2);else

   if height <60 and height >57 then height2=put (height,comma6.2);else

    height2=put (height,percent6.2);

ratio=height/weight;

if ratio > .7 then ratio2 = put (ratio,percent6.2);else ratio2=put (ratio,dollar6.2);

run;

ods HTML4 file='c:\temp\excelreport.xls';

  proc report data=work.class  nowd LS=160 PS=50 SPLIT="*" contents=''

        style(report)={ bordercolor=grey font_size=11pt }

        style(column)={ bordercolor=#ddddff font_size=11pt bordercolor=grey  cellspacing=1}

        style(header)={background=#000075 foreground=white cellheight=40 font_size=12pt};

column  name sex age height2 weight ratio2 ;

define name / 'Name';

define sex / 'Sex';

define age / 'Age';

define height2 / display 'Height' ;

define weight / display 'weight' format=comma6.2;

define ratio2  / display  'Ratio';

run;

ods _all_ close;

This can give you a mixed format within a column.

Name	Sex	Age	Height	weight	Ratio
Alfred	M	14	$69.00	112.50	$0.61
Alice	F	13	6E3%	84.00	$0.67
Barbara	F	13	$65.30	98.00	$0.67
Carol	F	14	$62.80	102.50	$0.61
Henry	M	14	$63.50	102.50	$0.62
James	M	12	57.30	83.00	$0.69
Jane	F	12	59.80	84.50	71%
Janet	F	15	$62.50	112.50	$0.56
Jeffrey	M	13	$62.50	84.00	74%
John	M	12	59.00	99.50	$0.59
Joyce	F	11	5E3%	50.50	102%
Judy	F	14	$64.30	90.00	71%
Louise	F	12	6E3%	77.00	73%
Mary	F	15	$66.50	112.00	$0.59
Philip	M	16	$72.00	150.00	$0.48
Robert	M	12	$64.80	128.00	$0.51
Ronald	M	15	$67.00	133.00	$0.50
Thomas	M	11	57.50	85.00	$0.68
William	M	15	$66.50	112.00	$0.59

[Cynthia_sas]

Hi:

  You can actually do a lot of this formatting in a COMPUTE block without creating character versions of the variables. See the program below. It doesn't do the exact same things, but should be enough to give you an idea of how it would be done.

  If you want something like this demographic example, on the other hand:

Gender        N (PctN)  Age Range

     F        9 (47%)    11-15

     M       10 (53%)    11-16

where you have 2 statistics (N and PctN) concatenated together in 1 column on the report or the min/max of age formatted with a dash between them, then you might need to create a character variable prior to your report step or PROC REPORT might be able to compute the character variable for you. I showed an example (several examples) of doing this in my SUGI paper entitled "Creating Complex Reports", which you can find by searching support.sas.com web site. Also, the second step below produces the above example report (with N/PctN and Age Range).

cynthia

ods listing close;

ods html file='c:\temp\diff_fmt.html' style=sasweb;

proc report data=sashelp.class nowd;

   column name sex age height weight ratio;

   define name / order;

   define age / display;

   define height /sum;

   define weight / sum;

   define ratio /computed f=percent9.2;

   compute height;

     if sex = 'F' then do;

        call define(_col_,'format','percent9.2');

     end;

   endcomp;

   compute ratio;

     ratio = height.sum / weight.sum;

     if ratio gt .7 then do;

       call define(_col_,'format','dollar9.2');

     end;

   endcomp;

run;

ods html close;

ods html file='c:\temp\calc_cols.html' style=sasweb;

proc report data=sashelp.class nowd;

   column sex n pctn dispstats age=minage age=maxage disprange;

   define sex / group;

   define n / noprint;

   define pctn / noprint;

   define minage / min noprint;

   define maxage / max noprint;

   define dispstats /computed 'N (PctN)'

          style(column)={just=r};

   define disprange / computed 'Age Range'

          style(column)={just=r};

   compute dispstats/ character length=20;

     dispstats = cat(put(n,2.0),' (',put(pctn,percent6.),')');

   endcomp;

   compute disprange/ character length=20;

     disprange = catx('-',put(minage,2.0),put(maxage,2.0));

   endcomp;

run;

ods html close;