My code: proc sql;
create table codefees as
select
a.Code format $7. informat $7. length 7
,b.Description
,b.Count as AR_Count label="AR Counts"
,a.ST1 as AR_AMT format dollar10.2 label="AR Amount"
,a.ST2 as MS_AMT format dollar10.2 label="MS Amount"
,round(a.ST1-ST2, .01) as MS_AMT_DIFF format dollar10.2 label="MS Amount Diff"
,round(calculated MS_AMT_DIFF/a.ST1, .01) as MS_AMT_PCT format percentn8.2 label="MS Percent Diff"
,a.ST3 as MO_AMT format dollar10.2 label="MO Amount"
,round(a.ST1-ST3, .01) as MO_AMT_DIFF format dollar10.2 label="MO Amount Diff"
,round(calculated MO_AMT_DIFF/a.ST1, .01) as MO_AMT_PCT format percentn8.2 label="MO Percent Diff"
,a.ST4 as OK_AMT format dollar10.2 label="OK Amount"
,round(a.ST1-ST4, .01) as OK_AMT_DIFF format dollar10.2 label="OK Amount Diff"
,round(calculated OK_AMT_DIFF/a.ST1, .01) as OK_AMT_PCT format percentn8.2 label="OK Percent Diff"
,a.ST5 as TX_AMT format dollar10.2 label="TX Amount"
,round(a.ST1-ST5, .01) as TX_AMT_DIFF format dollar10.2 label="TX Amount Diff"
,round(calculated TX_AMT_DIFF/a.ST1, .01) as TX_AMT_PCT format percentn8.2 label="TX Percent Diff"
,a.ST6 as LA_AMT format dollar10.2 label="LA Amount"
,round(a.ST1-ST6, .01) as LA_AMT_DIFF format dollar10.2 label="LA Amount Diff"
,round(calculated LA_AMT_DIFF/a.ST1, .01) as LA_AMT_PCT format percentn8.2 label="LA Percent Diff"
from CodeFees_1m as a
left join Codefees_2 as b
on a.Code = b.Code
;
quit;
ods excel file="&root.Procedure\Revised_table.xlsx"
options(autofilter="1-19" sheet_name="State Comparison");
proc print data=codefees label;
run;
ods excel close;
... View more
