topic Re: Proc summary YTD mean problem in New SAS User

Proc summary YTD mean problem

PierreYvesILY — Fri, 16 Apr 2021 11:49:03 GMT

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,

Re: Proc summary YTD mean problem

PaigeMiller — Fri, 16 Apr 2021 11:56:22 GMT

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.

Re: Proc summary YTD mean problem

FreelanceReinh — Fri, 16 Apr 2021 16:20:40 GMT

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;

Re: Proc summary YTD mean problem

mkeintz — Sat, 17 Apr 2021 13:34:17 GMT

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;

Re: Proc summary YTD mean problem

PierreYvesILY — Mon, 19 Apr 2021 13:02:30 GMT

very good tip, thanks a lot!

Re: Proc summary YTD mean problem

PierreYvesILY — Mon, 19 Apr 2021 13:03:00 GMT

I'll try this and send you feedback

Re: Proc summary YTD mean problem

PierreYvesILY — Fri, 23 Apr 2021 14:10:23 GMT

hello Reinhardt,

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

This is not the right way, unfortunately.

Re: Proc summary YTD mean problem

PierreYvesILY — Fri, 23 Apr 2021 14:14:01 GMT

sorry, this post was for mkeintz