calculating change in percentage- Proc tabulate.

Richard

2009-03-16 19:31:37 UTC

Post by k***@gmail.com
Hi,
I am trying to calculate change in percentage in a Proc Tabulate. I
have two totals in two different tables obtained by using Proc
Tabulate for each one of them. Now I would request you to help me out
in calculating the percentage difference in both the totals.
Table 1:Year 2007
Jan Feb Mar Total
a 10 20 10 40
b 5 10 15 30
c 10 20 10 40
Table 2: Year 2008
Jan Feb Mar Total
a 5 10 15 30
b 5 10 15 30
c 10 20 10 40
I would like to find the percentage change in totals in 2008 when
compare to 2007. I have used PROC Tabulate to get these two tables
individually.

TABULATE does not have the capability to perform the percentage change
calculations. You can save the tabulation results and perform your
own computations and then report them via tabulate. Proc REPORT via
its compute blocks might be able to do everything in a single step,
however it would likely be overly complicated. This sample
demonstrates a multi-step tabulate way:

------------------------------------------------
data foo;
do date = '01jan06'd to '01jan09'd - 1;
do group = 'a','b','c';
if ranuni(1234)<0.5 then
x = floor (25*ranuni(1234));
else
x = 0;

output;
end;
end;

format date date9. x 4.;
run;

ods escapechar='^';

ods listing close;
ods html
file="%sysfunc(pathname(WORK))\report.html"
style=journal
;

data foo_v / view=foo_v;
set foo;
year = date;
qtr = qtr(date);
run;

proc format;
value qcolor
1 = 'cxeeeeff'
2 = 'cxc8c8f8'
3 = 'cxbbbbf0'
4 = 'cx9898e8'
;
run;

title;
footnote;

proc tabulate data=foo_v out=crosstab_summary;
class year qtr date group;
classlev qtr / style=[background=qcolor.];
format
year year.
date monname3.
;
var x / style=<PARENT>;
keyword all / style=<PARENT>;
keyword sum / style=<PARENT>;
table
year
, group
, qtr
* (date=''
all='Total' * [style=<PARENT>]
)
* x='' * sum='' * f=4.
;
run;

proc sort data=crosstab_summary out=crosstab_alls;
by qtr group year;
where _type_ = '1101';
run;

data pct_change_alls(keep=year qtr group pct_change);
base = .;
do _n_ = 0 by 1 until (last.group);
set crosstab_alls;
by qtr group;

pct_change = (x_sum - base) / base;
if _n_ then OUTPUT;

base = x_sum;
end;

format pct_change PERCENT5.1;
run;

proc format;
value updown
low - < -1.0 = 'cxff0000'
-1.0 - < -0.5 = 'cxff8888'
-0.5 - < 0.0 = 'cxffdddd'
0.0 = 'white'
0.0 < - 0.5 = 'cxddffdd'
0.5 < - 1.0 = 'cx88ff88'
1.0 < - high = 'cx00ff00'
;
run;

proc tabulate data=pct_change_alls;
class year qtr group;
var pct_change;
label qtr = 'qtr (totals), % change from prior year';
table
year='' , group=''
, qtr * pct_change='' * mean='' * f=PERCENT5.1 * [style=
[background=updown.]]
;
run;

ods html close;
------------------------------------------------

Richard A. DeVenezia
http://www.devenezia.com