Hi SAS users.
I am trying to generate a numeric summary table using proc report.
Here is the code I run and I have attached the data set I use.
%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 Small_Business_Failure p_s _FREQ_=pct);
DEFINE &var /ORDER "&var";
DEFINE _FREQ_ /ANALYSIS "Frequency" FORMAT=comma10.;
DEFINE pct /ANALYSIS PCTSUM "Pop" FORMAT=percent6.1;
DEFINE p_c /COMPUTED "p_c" FORMAT=percent6.1;
DEFINE index /COMPUTED "index" FORMAT=comma10.;
DEFINE p_s /COMPUTED "P_s" FORMAT=percent6.1;
COMPUTE p_c;
p_c=Consumer_Default.sum/_FREQ_.sum;
ENDCOMP;
COMPUTE p_s;
p_s=Small_Business_Failure.sum/_FREQ_.sum;
ENDCOMP;
COMPUTE AFTER;
&var = 'Total';
ENDCOMP;
RBREAK AFTER /SUMMARIZE OL;
RUN;
%mend;
%table_generator(var=industry);
Running the code generates thef following table.
industry | Frequency | Consumer Default | p_c | index | Small Business Failure | P_s | Pop |
---|---|---|---|---|---|---|---|
Agriculture | 10 | 0 | 0.0% | . | 0 | 0.0% | 2.9% |
Construction | 52 | 1 | 1.9% | . | 1 | 1.9% | 15% |
Finance | 41 | 3 | 7.3% | . | 0 | 0.0% | 12% |
Manufacturing | 34 | 3 | 8.8% | . | 0 | 0.0% | 9.8% |
Retail | 78 | 3 | 3.8% | . | 0 | 0.0% | 22% |
Service | 111 | 8 | 7.2% | . | 1 | 0.9% | 32% |
Transportation | 4 | 0 | 0.0% | . | 0 | 0.0% | 1.1% |
Wholesale | 18 | 3 | 17% | . | 0 | 0.0% | 5.2% |
Total | 348 | 21 | 6.0% | . | 2 | 0.6% | 100% |
What I want now is, for the each row of index entry , I want to calculate index, which I define by
Index = p_c / (total p_c) *100.
So for example, for the construction index i would calculate the index by
index_construction = 1.9%/6.0%*100 = 31.666666.
How would you go about doing this in proc reort?
Many thanks
I am trying to
So, that takes care of #1, what about #2? What destination? and #3? The formula for the calculation of INDEX?
cynthia
you'd better to post some sample data, then Cynthia or others can test it more.
But I think you can code like something:
proc report data=sashelp.class nowd; column name height weight x Index ; define name/order; define height/analysis; define weight/analysis; define x/computed; define Index /computed; compute x; x=weight.sum/height.sum; endcomp; compute before; sum=x; endcomp; compute Index ; Index=x/sum; endcomp; rbreak after/summarize ol;run;
Ksharp
Hi:
A few other observations:
1) I agree with Ksharp -- you showed the output from PROC REPORT, but no data -- anyone who tries to help you will either have to make fake data or use SASHELP datasets.
2) You did not specify a destination of choice (RTF, PDF, HTML, LISTING) some of your code options, such as OL are LISTING only options, but you show ODS STYLE= overrides - -which makes me think you have a non-LISTING destination in mind.
3) you show this division for the calculation of INDEX (using the rounded, displayed, formatted numbers):
1.9%/6.0%*100 = 31.666666
However, the numbers are not internally stored as rounded numbers with a shifted decimal point, and SAS is not dividing by the rounded numbers, as displayed using the percent format. The calculation might be closer to something like this because SAS and PROC REPORT will use the INTERNAL values for the columns:
0.019230769231 / 0.060344827586 = 0.3186813
4) You show the proposed division as though INDEX_CONSTRUCTION is the variable you are creating. However, you must use the COMPUTED column name in your formula in the COMPUTE block. So if the variable in the COLUMN statement is INDEX, then INDEX is what you would use in your COMPUTE block.
If you want to display this number with a % sign, then you should NOT use the extra multiply by 100 in your calc, because the PERCENT format will do a multiply by 100, too. If you do not want a % in the displayed number, then the multiply by 100 in your formula is correct. If you want to divide using rounded numbers, then you must round them as you do the divide.
5) What you want to do is entirely do-able with PROC REPORT. You will need to capture the totals for each column (such as p_c) probably in a COMPUTE BEFORE and store the values in temporary variables, so they are automatically retained so you can do the extra division you need.
cynthia
My apologies for forgetting the dataset. I have now attached it with the first post.
Thanks for the replying Cynthia and Ksharp.
So, that takes care of #1, what about #2? What destination? and #3? The formula for the calculation of INDEX?
cynthia
%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!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.