topic Re: Tabulate Statistics in SAS Procedures

Tabulate Statistics

Jackie_Stanbank — Mon, 24 May 2010 11:17:52 GMT

i have created a tabulate with two dfferent dates' data as values (example below):
sumary data:
DPD Date _freq_
1 20/05/2010 500
30 20/05/2010 500
60 20/05/2010 500
1 21/05/2010 400
30 21/05/2010 400
60 21/05/2010 400

tabulate result
DPD 20/05/2010 21/05/2010
1 500 400
30 500 400
60 500 400

code
PROC TABULATE DATA= jackie.oview_201 format=comma13.;
CLASS DPD DATE ;
VAR _freq_;
TABLE DPD,Date*_freq_;
TITLE 'Late Stage Overview Number';
label _freq_ =Number;
RUN;

how do i get a variance between the two figures if it is a result of the tabulate?
Desired Result:
DPD 20/05/2010 21/05/2010 Variance
1 500 400 100
30 500 400 100
60 500 400 100

also, i want my ods output in excel to reflect the data with a white background
(i already have the headings under control).

Re: Tabulate Statistics

Cynthia_sas — Tue, 25 May 2010 11:35:11 GMT

Hi:
TABULATE will not automatically calculate "new" columns, such as your difference or variance. PROC REPORT has a compute block which does allow REPORT (on the other hand) to calculate new report items that are not in the original input dataset. So your choices are:
1) precalculate the variance in a dataset (which may mean you need to restructure your dataset in order to calculate the variance and then pass the new dataset to TABULATE
or
2) switch to PROC REPORT and investigate ACROSS usage for report items and using a COMPUTE block to calculate new report items.

cynthia

Re: Tabulate Statistics

Peter_C — Mon, 31 May 2010 09:15:13 GMT

Jackie
in the "ODS and Base Reporting" SAS discussion forum at http://support.sas.com/forums/forum.jspa?forumID=6 you'll find discussions describing how to have sas write formulas (rather than data) into excel, where that variance could be calculated. It is a feature that can be defined for the tagattr within a style element for the ods destination tagsets.excelxp.
Here http://support.sas.com/forums/click.jspa?searchID=152829&messageID=10116 is a message posted by Cynthia that calculates variance between two columns in excel
hope you'll give it a try

peterC