I need PROC REPORT to summarize to a weighted average! Arrrgh!

[Aaronlane]

I'm having a lot of success with PROC REPORT, but I have one major hangup. First off, my data is single family and multi family housing completions by county. So I have a column of SF completions in 2012, then a computed column with the V%, or percent change from 2011 to 2012. So far so good. But the summary line needs to be the sum of this year's housing starts (Again, easy...) and the V% of those counties from last year to this year.

I've tried using: define SF_TOT_V / analysis mean weight=sftall 'V%' Width=4 format=8.0;

where sftall is the sum of housing starts for 2011 & 2012. I've tried using EVERYTHING as this weight. I've gotten nothing.

Anyone who has ever had to do this before? My code is attached here:

data Contract;

retain salescode cnty_stat TotalV sf2012 SF_V sf_tot SF_TOT_V sfcompl2012 SF_COMP_V mf2012 MF_V mf_tot MF_TOT_V mfcompl2012 MF_COMP_V

    TotDir TotDirV TotUnits TotUnitsV TotComp ;

set alm1;

sf_tot = round(sft2012,1);

mf_tot = round(mft2012,1);

SF_V = round(divide((sf2012-sf2011),sf2011) *100,1);

sftall=(sft2011+sft2012);

SF_TOT_V = round(divide((sf_tot-sft2011),sft2011)*100,1);

SF_COMP_V = round(divide((sfcompl2012-sfcompl2011),sfcompl2011)*100,1);

MF_V = round(divide((mf2012-mf2011),mf2011)*100,1);

MF_TOT_V = round(divide((mf_tot-mft2011),mft2011)*100,1);

MF_COMP_V = round(divide((mfcompl2012-mfcompl2011),mfcompl2011)*100,1);

TotDir = sf2012 + mf2012;

TotDir2011 = sf2011 + mf2011;

TotDirV = round(divide((TotDir - TotDir2011),TotDir2011)*100,1);

TotUnits = sft2012 + mft2012;

TotUnits2011 = sft2011 + mft2011;

TotUnitsV = round(divide((TotUnits - TotUnits2011),TotUnits2011)*100,1);

TotComp = sfcompl2012 + mfcompl2012;

TotComp2011 = sfcompl2011 + mfcompl2011;

TotComp2012 = sfcompl2012 + mfcompl2012;

TotalV = round(divide((TotComp - TotComp2011),TotComp2011)*100,1);

mftall = mft2012+mft2011;

keep salescode cnty_stat TotalV sf2012 SF_V sft2012 SF_TOT_V sfcompl2012 SF_COMP_V mf2012 MF_V mft2012 MF_TOT_V mfcompl2012 MF_COMP_V

    TotDir TotDirV TotUnits TotUnitsV TotComp srsregn cregname mftall sftall;

run;

/************************************************************************************/

/************************************************************************************/

ods rtf file='C:\Documents and Settings\aaronm\Desktop\DP186\TESTING.doc';

proc report data=Contract nowindows;

title1 'Contract Performance Summary - Total Share Products';

title2 'Operation: &Name Region: &Region';

title3 'YTD &Month, &Year';

columns salescode cnty_stat  sf2012 SF_V sft2012 SF_TOT_V sfcompl2012 SF_COMP_V mf2012 MF_V mft2012 MF_TOT_V mfcompl2012 MF_COMP_V

    TotDir TotDirV TotUnits TotUnitsV TotComp TotalV sftall mftall;

define Cnty_Stat / display 'County' Width=25;

define sf2012 / analysis sum 'SF Dir' Width=4 format=8.0;

define SF_V / analysis mean   'V%' Width=4 format=8.0;

define sft2012 / analysis sum 'SF Total' Width=4 format=8.0;

define SF_TOT_V / analysis mean weight=sftall 'V%' Width=4 format=8.0;

define sfcompl2012 / analysis sum 'SF Comp' Width=4 format=8.0;

define SF_COMP_V / analysis mean weight=sfcompl2012 'V%' Width=4 format=8.0;

define mf2012 / analysis sum 'MF Dir' Width=4 format=8.0;

define MF_V / analysis mean weight=mf2012 'V%' Width=4 format=8.0;

define mft2012 / analysis sum 'MF Tot' Width=4 format=8.0;

define MF_TOT_V / analysis mean weight=mftall 'V%' Width=4 format=8.0;

define mfcompl2012 / analysis sum  'MF Comp' Width=4 format=8.0;

define MF_COMP_V / analysis mean weight=mfcompl2012 'V%' Width=4 format=8.0;

define TotDir / analysis sum 'Tot Dir' Width=4 format=8.0;

define TotDirV / analysis mean weight=TotDir 'V%' Width=4 format=8.0;

define TotUnits / analysis sum 'Tot Units' Width=4 format=8.0;

define TotUnitsV / analysis mean weight=TotUnits 'V%' Width=4 format=8.0;

define TotComp / analysis sum 'Tot Comp' Width=4;

define TotalV / display 'V%' Width=4 format=8.0;

define sftall / noprint;

define salescode / group;

compute Cnty_Stat;

bg +1;

if mod(bg,2)=1 then

call define(_row_, "style", "Style={background=white}");

else

call define(_row_, "style", "Style={background=#d1e9d1}");

endcomp;

break after salescode /  page summarize;

run;

[Ksharp]

Can you post a simple sample data , and the output or result you need .

Your code is too long to read. and you have so many 'weight=', I suggest you to process your data firstly by using data step.

Ksharp

[AUTigers]

I never used WEIGHT option to calculate the weight mean. here is an idea, can you define a computed var to accumulate SF_TOT_V based on the weight variable? the SF_TOT_V and SFTALL defined with noprint option.

[Aaronlane]

Here is a sample dataset:

	What I want it to ouput:				How it looks instead
	SF_2011	SF_2012	V%		SF_2011	SF_2012	V%
	19	17	-12		19	17	-12
	13	121	838		13	121	838
Total	32	138	328	Total	32	138	736

Note that 328 is the "right answer." Between 2011 and 2012 housing starts have increases from 32 to 138, an increase of 328%. Using rbreak summarize, I can ONLY calculate using the -12 and the 838 in the column... So frustrating.

[Cynthia_sas · Accepted solution]

Hi:

  PROC REPORT is quite capable of doing a calculation on the RBREAK. For example, if I look at SASHELP.CLASS and only at ages 12 and 14, I can sum up the HEIGHT and WEIGHT variables and do a division. (Silly calculation, I know, but I wanted to keep it simple, since you don't provide any data and it almost looks to me like you are trying to "precalculate" everything in a DATA step program instead of letting REPORT do the work.)

  So, here's the program and the output is shown below in the screenshot. At the RBREAK, the sum of all the HEIGHT values is 556.8 and the sum of all the WEIGHT values is 879.5 if I subtract those 2 values, (with my trusty calculator), I get 322.7 -- and then if I divide 322.7 (the difference) by 879.5 (the sum of the WEIGHT variable column), I get 0.3669130187606595 which, when formatted with the PERCENT format (which multiplies by 100), displays as   36.69%. This proves to me that at the RBREAK, PROC REPORT is correctly calculating what I have in the COMPUTE block.

  Numeric variables are treated as though you want a SUM for the analysis usage -- so if you have "precalculated" your numbers, then in the V% column, PROC REPORT has no choice but to add up those numbers. But, as you can see, if you let PROC REPORT do the work, it will execute a COMPUTE block correctly at the RBREAK. Since I'm not entirely sure what's happening with your DATA step program, it's hard to provide constructive advice about the overall method or code your using. However, Tech Support could look at ALL your data and ALL your code and help you come to a resolution.

cynthia

** sort of silly to calc a percent using student WEIGHT and HEIGHT ;

** variables but the numbers are easy to double check;

** DIFF is calculated separately just for the sake;

** of checking the numbers;

** and FAKEPCT is the DIFF divided by the number for the WEIGHT variable;

proc report data=sashelp.class nowd;

  where age in (12, 14);

  column age sex n height weight diff fakepct;

  define age / group;

  define sex / group;

  define height / sum;

  define weight / sum;

  define diff / computed;

  define fakepct / computed f=percent9.2;

  compute diff;

     diff = weight.sum - height.sum;

  endcomp;

  compute fakepct;

     fakepct = diff / weight.sum;

  endcomp;

  rbreak after / summarize;

run;

ods html close;

[Aaronlane]

Ohhh Cynthia... I sing unto your praises... my Proc Report is beeeeaaaautiful now!

99	DEL NORT, CA	0	.	0	.	4	( 43%)
	HUMBOLDT, CA	2	( 33%)	17	( 14%)	74	( 13%)
	LAKE    , CA	0	.	0	.	19	6%
	MENDOCIN, CA	0	.	121	840%	49	( 9%)
	TRINITY , CA	0	.	0	.	19	(    10%)
99		2	( 33%)	138	328%	165	( 11%)

proc report data=Contract nowindows;

title1 'Contract Performance Summary - Total Share Products';

title2 'Operation: &Name Region: &Region';

title3 'YTD &Month, &Year';

columns salescode cnty_stat  sf2011 sf2012 TryV sft2011 sft2012 TV sfcompl2011 sfcompl2012 sfcompV diff;

define Cnty_Stat / display 'County' Width=25;

define sf2011 / noprint;

define sf2012 / sum 'SF Dir' Width=4 format=8.0;

define diff / computed noprint;

define TryV / computed f=percent9.;

define sft2011 / sum noprint;

define sft2012 / analysis sum 'SF Total' Width=4 format=8.0;

define TV / computed f=percent9.;

define sfcompl2011 / sum noprint;

define sfcompl2012 / analysis sum 'SF Comp' Width=4 format=8.0;

define sfcompV / computed f=percent9.;

define salescode / group;

compute diff;

    diff = sf2012.sum - sf2011.sum;

    endcomp;

compute TryV;

    TryV = (sf2012.sum-sf2011.sum) / sf2011.sum;

    endcomp;

compute TV;

    TV = (sft2012.sum-sft2011.sum) / sft2011.sum;

    endcomp;

compute sfcompV;

    sfcompV = (sfcompl2012.sum-sfcompl2011.sum) / sfcompl2011.sum;

    endcomp;

compute Cnty_Stat;

bg +1;

if mod(bg,2)=1 then

call define(_row_, "style", "Style={background=white}");

else

call define(_row_, "style", "Style={background=#d1e9d1}");

endcomp;

break after salescode /  page summarize;

run;

[TomKari]

You're not the only one...we ALL sing Cynthia's praises!!!

Tom