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.
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
Thanks
Hi zqkal
I think the following paper will help you.
http://www.lexjansen.com/pharmasug/2010/sas/sas-hw-sas01.pdf
Goodluck
Mit
Thanks
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.