%macro table_generator(var); PROC REPORT DATA= &var NOWD STYLE(header) = [FONT=(Arial,2) JUST=CENTER FONT_WEIGHT=BOLD] STYLE(report) = [FONT=(Arial,1.8) BORDERWIDTH=0.2 CELLSPACING=0.1 JUST=CENTER WIDTH=980] STYLE(column) = [FONT=(Arial,2) JUST=CENTER] STYLE(summary)=[FONT=(Arial,2.5) FONT_WEIGHT=BOLD]; COLUMN (&var _FREQ_ Consumer_Default p_c index_c Small_Business_Failure p_s _FREQ_=pct); DEFINE &var /ORDER "&var"; DEFINE _FREQ_ /ANALYSIS "Frequency" FORMAT=comma10.; DEFINE p_c /COMPUTED "p_c" FORMAT=percent6.1; DEFINE index_c /COMPUTED "index_c" FORMAT=comma10.; DEFINE p_s /COMPUTED "p_s" FORMAT=percent6.1; DEFINE pct /ANALYSIS PCTSUM "Pop" FORMAT=percent6.1; COMPUTE BEFORE; T=SUM(T,p_c); ENDCOMP; COMPUTE p_c; p_c=Consumer_Default.sum/_FREQ_.sum; ENDCOMP; COMPUTE index_c; index_c=p_c/T*100; ENDCOMP; COMPUTE AFTER; &var = 'Total'; ENDCOMP; RBREAK AFTER /SUMMARIZE OL; RUN; %mend; %table_generator(var=industry); I followed your hint and modifed the first block into above and this gave me what I wanted. The red bits are the bits I have amended/added to calculate the index. As for the points that Cynthia raised: 2) I wanted to the output in the excel sheet but I copy and paste from sas into my exisiting spreadsheet so this is ok now. Actually, I would like to look into this later so I can automate the whole process. 3) I actually do not want any intermediate rounding before get my index. So I am happy with how SAS keeps and uses the original values for index_construcion formula. Sorry for causing confusion in the formula. 4) I have changed the format of the index into comma9. , so I would get numbers without % symbol. Thank you so much This would save a lot of time for the work I am doing and I see myself re-using this in the future over and over. How great!
... View more