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
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.