topic Need help with Proc Report in SAS Programming

Need help with Proc Report

smoorman — Wed, 26 Aug 2020 20:31:14 GMT

I have a dataset that includes product, hh_count and balances. I am trying to use hh_count and balances to get an average balance and then a total at the bottom.

Here is my code:

proc report data=bal_change2
style(report)={BORDERCOLOR=black BORDERWIDTH=1}
style(column)={BORDERCOLOR=black}
style(header)={BORDERCOLOR=black background = #DBE5F1 foreground=black}
style(summary)={font_weight=bold font_style=Roman};
options missing = ' ';
columns ('Deposit Balance Stratification 2020' (prod_group hh_cnt) strat_bl, (hh_cnt=hh_cnt2 balance_incr avg_bal_incr2));
define prod_group / Group left 'Product';
define hh_cnt / analysis 'HH Count' format=comma12.0;
define strat_bl / across 'Tier';
define hh_cnt2 / analysis 'HHs' format=comma12.0;
define balance_incr / analysis 'Balance Incr' format=dollar12.0;
define avg_bal_incr2 / computed 'Avg Balance Increase' format=dollar12.0;
compute avg_bal_incr2;
avg_bal_incr2=balance_incr/hh_cnt2;
endcomp;
rbreak after / summarize;
run;

The result I am getting for average balance is blank, but I can't figure out what I'm doing wrong.

Deposit Balance Stratification 2020
	Tier
	$0 - $1,000	$1,000 - $2,500
Product	HH Count	HHs	Balance Incr	Avg Balance Increase	HHs	Balance Incr
Enhanced	228,008	21,936	$40,775,269		30,539	$70,894,035
Essential	1,401,419	569,177	$538,610,905		315,773	$558,937,924
Express	101,736	96,943	$49,936,179		3,109	$4,390,152
Free	234,756	174,102	$134,095,346		31,062	$52,489,296
Other	40,763	27,332	$19,941,200		5,502	$10,131,858
Preferred	153,572	14,036	$43,309,240		14,252	$43,641,635
	2,160,254	903,526	$826,668,139		400,237	$740,484,900

Re: Need help with Proc Report

ChrisNZ — Thu, 27 Aug 2020 05:36:34 GMT

Your variable is summed, so the syntax is:

proc report data=SASHELP.CLASS;
  columns AGE WEIGHT AVG;
  define AGE   / group;
  define WEIGHT/ analysis sum;
  define AVG   / computed;
  compute AVG;
    AVG=WEIGHT.sum/2;
  endcomp;
run;

Age	Weight	AVG
11	135.5	67.75
12	472	236
13	266	133
14	407.5	203.75
15	469.5	234.75
16	150	75

Re: Need help with Proc Report

smoorman — Thu, 27 Aug 2020 14:34:42 GMT

Thank you for the help. When I modify my code as you suggested, I get the following error:
ERROR: The variable type of 'BALANCE_INCR.SUM'n is invalid in this context.
Any thoughts?

Re: Need help with Proc Report

ballardw — Thu, 27 Aug 2020 15:22:33 GMT

When you get an error please copy the entire procedure and all notes, warning or errors from the LOG and paste into a code box opened on the forum with the </> icon. The code box is important because the log often contains diagnostic characters and the positions are important but the main message windows will reformat text.

And why are you using 'BALANCE_INCR.SUM'n? That would indicate that you already have a variable in your data set, or are creating one with that name. I would expect BALANCE_INCR.SUM to work just fine since Balance_incr is a standard SAS variable name. The statistic references in proc report using the .<statistic> do not have the statistic as part of the variable name.

Re: Need help with Proc Report

smoorman — Thu, 27 Aug 2020 15:44:42 GMT

Sorry for the omission. I really appreciate your reply. My code is below, along with the error log. Any help is appreciated.
Code:
proc report data=bal_change2
style(report)={BORDERCOLOR=black BORDERWIDTH=1}
style(column)={BORDERCOLOR=black}
style(header)={BORDERCOLOR=black background = #DBE5F1 foreground=black}
style(summary)={font_weight=bold font_style=Roman};
options missing = ' ';
columns ('Deposit Balance Stratification 2020' (prod_group hh_cnt) strat_bl, (balance_incr avg_bal_incr2));
define prod_group / Group left 'Product';
define hh_cnt / analysis sum 'HH Count' format=comma12.0;
define strat_bl / across 'Tier';
define balance_incr / analysis sum 'Balance Incr' format=dollar12.0;
define avg_bal_incr2 / computed 'Avg Balance Increase' format=dollar12.0;
compute avg_bal_incr2;
avg_bal_incr2=balance_incr.sum/hh_cnt.sum;
endcomp;
rbreak after / summarize;
run;

Error:
proc report data=bal_change2
80 style(report)={BORDERCOLOR=black BORDERWIDTH=1}
81 style(column)={BORDERCOLOR=black}
82 style(header)={BORDERCOLOR=black background = #DBE5F1 foreground=black}
83 style(summary)={font_weight=bold font_style=Roman};
84 options missing = ' ';
85 columns ('Deposit Balance Stratification 2020' (prod_group hh_cnt) strat_bl, (balance_incr avg_bal_incr2));
86 define prod_group / Group left 'Product';
87 define hh_cnt / analysis sum 'HH Count' format=comma12.0;
88 define strat_bl / across 'Tier';
89 define balance_incr / analysis sum 'Balance Incr' format=dollar12.0;
90 define avg_bal_incr2 / computed 'Avg Balance Increase' format=dollar12.0;
91 compute avg_bal_incr2;
92 avg_bal_incr2=balance_incr.sum/hh_cnt.sum;
93 endcomp;
94 rbreak after / summarize;
95 run;

ERROR: The variable type of 'BALANCE_INCR.SUM'n is invalid in this context.
NOTE: The preceding messages refer to the COMPUTE block for avg_bal_incr2.
NOTE: Will not run due to compilation errors.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: There were 36 observations read from the data set WORK.BAL_CHANGE2.
NOTE: PROCEDURE REPORT used (Total process time):
real time 0.02 seconds
cpu time 0.01 seconds

Re: Need help with Proc Report

ChrisNZ — Fri, 28 Aug 2020 04:41:59 GMT

Cant test as I don't have your data, but either syntax works for me:

proc report data=SASHELP.CLASS;
  columns AGE WEIGHT HEIGHT AVG AVG1;
  define AGE   / group;
  define WEIGHT/ analysis sum;
  define AVG   / computed;
  compute AVG1;
    AVG=WEIGHT.sum/_C3_;
    AVG1=WEIGHT.sum/HEIGHT.sum;
  endcomp;
run;

Age	Weight	Height	AVG	AVG1
11	135.5	108.8	1.2454044	1.2454044
12	472	297.2	1.5881561	1.5881561
13	266	184.3	1.443299	1.443299
14	407.5	259.6	1.5697227	1.5697227
15	469.5	262.5	1.7885714	1.7885714
16	150	72	2.0833333	2.0833333