Proc summary YTD mean problem

[PierreYvesILY]

dear SAS experts,

I have to calculate then sums and means on a year to date Basis of a large amount of data. I use a Format suggested by Paige Miller and that works perfectly well:

* Format für die Berechnung der Durchschnitte für n Monate ;
proc format;
value ytd (multilabel) 
1							='Jan'
1,2							='Feb' 
1,2,3						='Mar'
1,2,3,4						='Apr'
1,2,3,4,5					='Mai'
1,2,3,4,5,6					='Jun'
1,2,3,4,5,6,7				='Jul'
1,2,3,4,5,6,7,8				='Aug'
1,2,3,4,5,6,7,8,9			='Sep'
1,2,3,4,5,6,7,8,9,10		='Oct'
1,2,3,4,5,6,7,8,9,10,11		='Nov'
1,2,3,4,5,6,7,8,9,10,11,12	='Dec'
; 
run;

My data are in a dataset with lots of variables - I'll post only Pictures of a limited view.

To calculate the YTD sums I used the following Code:

* Berechnung der monatlichen Durchschnitte ;
proc summary data=Dashboard_Primaerdaten;
class mr_group marktregion_bt nlbez_bt mbrbez_bt filbez_bt jahr ;
class monat/mlf;
var ORB: NGS: DrK: ;
types () 
jahr 
jahr*monat 
jahr*monat*mr_group
jahr*monat*mr_group*marktregion_bt 
jahr*monat*mr_group*marktregion_bt*nlbez_bt 
jahr*monat*mr_group*marktregion_bt*nlbez_bt*mbrbez_bt 
jahr*monat*mr_group*marktregion_bt*nlbez_bt*mbrbez_bt*filbez_bt
;
output out=FIL3_bis (drop=_:) sum=;
format monat ytd.;
run;

and got the following CORRECT results:

To calculate the YTD means, I used the following Code:

* Berechnung der monatlichen Durchschnitte ;
proc summary data=Dashboard_Primaerdaten;
class mr_group marktregion_bt nlbez_bt mbrbez_bt filbez_bt jahr ;
class monat/mlf;
var ORB: NGS: DrK: ;
types () 
jahr 
jahr*monat 
jahr*monat*mr_group
jahr*monat*mr_group*marktregion_bt 
jahr*monat*mr_group*marktregion_bt*nlbez_bt 
jahr*monat*mr_group*marktregion_bt*nlbez_bt*mbrbez_bt 
jahr*monat*mr_group*marktregion_bt*nlbez_bt*mbrbez_bt*filbez_bt
;
output out=FIL3_bis (drop=_:) mean=;
format monat ytd.;
run;

and I got the following results, which are NOT correct:

Obviously, the values of ORB_Anzahl_Akt are false ; the value for Jahr = 2000 and Monat = ' ' should be 332913 / 12 for instance, instead of 31.0119

What is the Problem in the Code?

How can I fix this?

Regards,

PY

[PaigeMiller]

When you ask for the mean, you get sum/N (that's how the mean is defined) which is 332913/N, and not 332913/12. You probably ought to do the calculation of 332913/12 in a DATA step.

[FreelanceReinh]

Hello @PierreYvesILY,

Just as a side note: Perhaps you aren't really happy with the alphabetical sort order of the month names in your PROC SUMMARY output (making it hard to recognize that the values are cumulative YTD sums). You can fix this by adding two more options to the CLASS statement for the month variable:

class monat / mlf order=data preloadfmt;

[PierreYvesILY]

very good tip, thanks a lot!

[mkeintz]

Instead of MONAT, consider using a YTD analog, via an intermediate data set view:

data vneed/ view=vneed;
  set Dashboard_Primaerdaten;
  do ytd_group=1 to monat; output; end;
run;

proc summary data=vneed;
  class mr_group marktregion_bt nlbez_bt mbrbez_bt filbez_bt jahr ytd_group ;
  ... 
run;

[PierreYvesILY]

I'll try this and send you feedback

[PierreYvesILY]

hello Reinhardt,

the proposed Code doesn't Change anything to my previous (false) results.

This is not the right way, unfortunately.

[PierreYvesILY]

sorry, this post was for mkeintz