How to highlight cell if the difference between two columns is greater than 7% in tagsets.Exelxp

[zqkal]

I have a dataset contains month to month differences. There will be new column added in front of the existing dataset every month.

I‘m outputting this dataset into excel file and would want to highlight the cell for the current month if the difference between current month and previous month is greater than 7%. 

example dataset:

Variable               col_201310          col_201309         col_201308          col_201309

A                           230                     200                     500                        23                        

B                           300                     200                     100                        50 

C                           112                    112                      225                        221

D                           12                      11                       23                          32

E                           11                       5                        88                          21

I would like to output this dataset into excel file and would want to highlight the cell if there is 7% difference between current month and previous month. Does anyone know who i could achive this in SAS?

This is the formula I used to calculate difference.

example.

( | 230 - 200 | / ((230 + 200)/2) ) * 100
= ( | 30 | / (430/2) ) * 100
= ( 30 / 215 ) * 100
= 0.139535 * 100

=13.9.

So highlight the cell.

[Jagadishkatam]

data have;

    input Variable$  col_201310  col_201309   col_201308  col_201307;

    diff=((col_201310-col_201309) / ((col_201310 + col_201309)/2) ) * 100;

cards;

A    230     200   500  23

B    300     200   100  50

C    112     112   225  221

D    12      11    23   32

E    11      5     88   21

;

ods listing close;

ods tagsets.excelxp file="M:\Practice\sample.xls";

proc report data=have nowd split = '^' ;

column Variable               col_201310          col_201309         col_201308          col_201309 diff;

define Variable /  'Variable';

define col_201310 / display 'col_201310' width=20;

define col_201309 / 'col_201309 ' width=20;

define col_201308 / 'col_201308' width=20;

define col_201309 / 'col_201309' width=20;

define diff / display  noprint;

compute diff;

if (diff >=7) then do;

call define ('col_201310','style','style=[background=yellow]');

end;

endcomp;

run;

ods _all_ close;

ods listing;

Thanks,

jagadish

[zqkal]

Thanks

[Mit]

Hi zqkal

I think the following paper will help you.

http://www.lexjansen.com/pharmasug/2010/sas/sas-hw-sas01.pdf

Goodluck

Mit

[zqkal]

Thanks