%let name=scen2; filename odsout '.'; %let male_color=vlib; %let fem_color=hotpink; %let box_color=graybb; /* renamed sheet from 'Scenario #2' to 'Scenario2' because of microsoft jett problem */ /* Read in: employee#, gender, grade, and annual salary */ PROC IMPORT DATAFILE= "scenario2.xls" DBMS=XLS OUT=mydata REPLACE; RANGE='Scenario2$A13:D113'; GETNAMES=YES; MIXED=NO; RUN; /* Read in the grade ranges: grade, bottom salary, and top salary */ PROC IMPORT DATAFILE= "scenario2.xls" DBMS=XLS OUT=grades REPLACE; RANGE='Scenario2$F13:H18'; GETNAMES=YES; MIXED=NO; RUN; data grades; set grades; length color $12 style $20; xsys='2'; ysys='2'; hsys='3'; when='A'; function='move'; x=grade-.1; y=bottom; output; function='draw'; size=.1; color="&box_color"; x=grade+.1; y=bottom; output; x=grade+.1; y=top; output; x=grade-.1; y=top; output; x=grade-.1; y=bottom; output; output; run; proc sql noprint; create table avgdata as select unique grade, gender, avg(annual_salary) format=dollar12.0 as avg_salary from mydata group by grade, gender; quit; run; data alldata; set mydata avgdata; /* Apply a little offset, to split the male/female individual obsns, and also move the avg triangles to the right */ if annual_salary^=. and gender='M' then grade=grade-.039; if annual_salary^=. and gender='F' then grade=grade+.039; if annual_salary=. and gender='M' then grade=grade+.15; if annual_salary=. and gender='F' then grade=grade+.15; run; proc transpose data=avgdata out=my_anno; by grade; run; proc datasets; modify my_anno; rename col1=female_salary; rename col2=male_salary; run; data my_anno1; set my_anno; length text $30 color style $20 html $500; xsys='2'; ysys='2'; hsys='3'; when='A'; function='label'; color='black'; size=2.25; style='albany amt'; position='6'; x=grade+.08; html= 'title='||quote( 'Pay Grade: '|| trim(left(round(grade))) ||'0D'x|| 'Gender: M '||'0D'x|| 'Average Salary: '|| trim(left(put(male_salary,dollar12.0))))|| ' href="scenario2.xls"'; y=male_salary+600; text=put(male_salary,dollar12.0); output; html= 'title='||quote( 'Pay Grade: '|| trim(left(round(grade))) ||'0D'x|| 'Gender: F '||'0D'x|| 'Average Salary: '|| trim(left(put(female_salary,dollar12.0))))|| ' href="scenario2.xls"'; y=female_salary+600; text=put(female_salary,dollar12.0); output; run; proc sql noprint; create table my_anno2 as select unique grade, ((male_salary-female_salary)/female_salary) as pct_dif from my_anno; quit; run; data my_anno2; set my_anno2; length style $20 color $12 text $ 30; xsys='2'; ysys='2'; hsys='3'; function='label'; color='black'; size=2.25; position='2'; x=grade; y=2800; style='albany amt/bold'; text=put(pct_dif,percent5.1); output; y=0; style='albany amt'; text='inequity'; output; run; data alldata; set alldata; length html $500; if (avg_salary^=.) then do; html= 'title='||quote( 'Pay Grade: '|| trim(left(round(grade))) ||'0D'x|| 'Gender: '|| trim(left(gender)) ||'0D'x|| 'Average Salary: '|| trim(left(put(avg_salary,dollar12.0))))|| ' href="scenario2.xls"'; end; else if (avg_salary=.) then do; html= 'title='||quote( 'Employee #: '|| trim(left(round(employee__))) ||'0D'x|| 'Pay Grade: '|| trim(left(round(grade))) ||'0D'x|| 'Gender: '|| trim(left(gender)) ||'0D'x|| 'Salary: '|| trim(left(put(annual_salary,dollar12.0))))|| ' href="scenario2.xls"'; end; run; data mydata; set mydata; format annual_salary dollar12.0; run; data legend; length function $8 color style $20 text $30; xsys='1'; ysys='1'; hsys='1'; when='A'; /* add dots ... */ function='label'; position='5'; style='albany amt/unicode'; text='25cb'x; size=4; x=4; y=96.2; color="&male_color"; output; y=y-3; color="&fem_color"; output; function='label'; style='albany amt/unicode'; text='25c4'x; size=3; y=y-3.6; color="&male_color"; output; y=y-3; color="&fem_color"; output; y=y-6; color="&box_color"; style='markere'; text='U'; size=4; output; function='label'; position='6'; color='black'; style='albany amt'; size=2.5; x=5.2; y=95.6; text='Male Salaries'; output; y=y-3; text='Female Salaries'; output; y=y-3; text='Male Average'; output; y=y-3; text='Female Average'; output; y=y-6; x=x+.3; text='Target Salary Grade Range'; output; run; data my_anno; set my_anno1 my_anno2 grades legend; run; goptions device=png; goptions noborder; ODS LISTING CLOSE; ODS HTML path=odsout body="&name..htm" (title="Scenario 2 for Stephen Few contest") style=minimal; goptions gunit=pct htitle=4.5 ftitle="albany amt/bold" htext=3.2 ftext="albany amt/bold"; goptions ctext=gray33; symbol1 c=&fem_color font="albany amt/unicode" v='25cb'x height=3 interpol=none; symbol2 c=&male_color font="albany amt/unicode" v='25cb'x height=3 interpol=none; symbol3 c=&fem_color font="albany amt/unicode" v='25c4'x height=2 interpol=none; symbol4 c=&male_color font="albany amt/unicode" v='25c4'x height=2 interpol=none; axis1 minor=none order=(0 to 120000 by 20000) label=none major=(h=-1) offset=(0,0); axis3 minor=none order=(0 to 120000 by 20000) major=(h=.0000001 c=white) label=none value=none offset=(0,0); axis2 label=('Salary Grade') order=(.5 to 6 by .5) major=(h=.0000001 c=white) minor=none value=(t=1 '' t=3 '' t=5 '' t=7 '' t=9 '' t=11 '' t=12 '') offset=(0,0); title1 ls=1.5 link="scen2_info.htm" "Salary Analysis"; title2 "Year 2004"; proc gplot data=alldata; format annual_salary dollar12.0; format avg_salary dollar12.0; format grade comma3.0; plot annual_salary*grade=gender / nolegend vaxis=axis1 haxis=axis2 autovref cvref=graycc lvref=2 html=html des='' name="&name" ; plot2 avg_salary*grade=gender / nolegend vaxis=axis3 anno=my_anno html=html; run; quit; ODS HTML CLOSE; ODS LISTING;