topic Re: How to highlight cell if the difference between two columns is greater than 7% in tagsets.Exelxp in SAS Programming

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

zqkal — Fri, 15 Nov 2013 00:23:12 GMT

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.

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

Jagadishkatam — Fri, 15 Nov 2013 02:58:28 GMT

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

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

Mit — Fri, 15 Nov 2013 03:20:08 GMT

Hi zqkal

I think the following paper will help you.

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

Goodluck

Mit

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

zqkal — Tue, 19 Nov 2013 15:09:56 GMT

Thanks

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

zqkal — Tue, 19 Nov 2013 15:10:09 GMT

Thanks