topic proc report using the calculated last row in SAS Procedures

proc report using the calculated last row

willy0625 — Wed, 15 Feb 2012 06:03:44 GMT

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

proc report using the calculated last row

Ksharp — Wed, 15 Feb 2012 09:26:24 GMT

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

proc report using the calculated last row

Cynthia_sas — Wed, 15 Feb 2012 16:39:52 GMT

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

Re: proc report using the calculated last row

willy0625 — Wed, 15 Feb 2012 21:34:12 GMT

My apologies for forgetting the dataset. I have now attached it with the first post.

Thanks for the replying Cynthia and Ksharp.

proc report using the calculated last row

Cynthia_sas — Wed, 15 Feb 2012 22:54:36 GMT

So, that takes care of #1, what about #2? What destination? and #3? The formula for the calculation of INDEX?

cynthia

Re: proc report using the calculated last row

willy0625 — Wed, 15 Feb 2012 23:12:32 GMT

%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!