DATA Step, Macro, Functions and more

3 Month totals sequence in proc report

Reply
Frequent Contributor
Posts: 131

3 Month totals sequence in proc report

data test;

infile datalines;

input Year $ Month $ Sales Returns ;

return;

datalines;

2014 Jan2014 1 1

2014 Feb2014 1 2

2014 Mar2014 1 3

2015 Apr2015 2 2

2015 May2015 1 1

2015 Jun2015 1 1

;

run;

proc report = test style(summary)=Header;

column Year Month Sales Returns ;

define Year /order order=data style (column)=Header;

define Sales /sum f=comma6.;

define Returns /sum f=comma6.;

break after year /summarize;

compute after Year;

line ' ';

endcomp;

run;

I want to get group totals every 3 months as follows:

1. Add sales to returns (sales+returns)
2. Divide Sales into the (sales+returns) figure and get a percentage called ActivityPct in 3 month intervals (example 3+6=9   then 3/9 = 33%

In this example I evaluate after Year.  However for the 3 month totalling I imagine I need a measure that evaluates after 3 months

Current Output

Year Month Sales Returns
2014 Jan2014 1 1
  Feb2014 1 2
  Mar2014 1 3
2014   3 6
 
2015 Apr2015 2 2
  May2015 1 1
  Jun2015 1 1
2015   4 4
 
Super User
Posts: 17,824

Re: 3 Month totals sequence in proc report

[ Edited ]

Can you create a QUARTER variable that would define your months/periods? You can use the NODISPLAY option to not have that variable shown in the report, even if you're using it.

If you have a date it can probably be derived from the date.

 

EDIT: Quarter does work exactly as expected. It's the NOPRINT option, not NODISPLAY.

 

data test;
infile datalines;
input Quarter Year $ Month $ Sales Returns ;
return;
datalines;
1 2014 Jan2014 1 1
1 2014 Feb2014 1 2
1 2014 Mar2014 1 3
2 2015 Apr2015 2 2
2 2015 May2015 1 1
2 2015 Jun2015 1 1
;
run;
proc report  data= test style(summary)=Header;
column quarter Year Month Sales Returns ;
define quarter / group noprint;
define Year /order order=data style (column)=Header;
define Sales /sum f=comma6.;
define Returns /sum f=comma6.;
break after quarter /summarize;
compute after quarter;
line ' ';
endcomp;
run;
Ask a Question
Discussion stats
  • 1 reply
  • 98 views
  • 1 like
  • 2 in conversation