BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
willy0625
Calcite | Level 5

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.

industryFrequencyConsumer Defaultp_cindexSmall Business FailureP_sPop
Agriculture1000.0% .00.0% 2.9%
Construction5211.9% .11.9% 15%
Finance4137.3% .00.0% 12%
Manufacturing3438.8% .00.0% 9.8%
Retail7833.8% .00.0% 22%
Service11187.2% .10.9% 32%
Transportation400.0% .00.0% 1.1%
Wholesale18317% .00.0% 5.2%
Total348216.0% .20.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

1 ACCEPTED SOLUTION

Accepted Solutions
Cynthia_sas
SAS Super FREQ

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

cynthia

View solution in original post

5 REPLIES 5
Ksharp
Super User

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

Cynthia_sas
SAS Super FREQ

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

willy0625
Calcite | Level 5

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

Thanks for the replying Cynthia and Ksharp.

Cynthia_sas
SAS Super FREQ

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

cynthia

willy0625
Calcite | Level 5

%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 Smiley Happy

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!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 1916 views
  • 6 likes
  • 3 in conversation