SQL AVG Function Case when on selective rows

[superking]

The dataset consist of multiple months (24+ months YYYYMM)

Pre6m_sales_avg is averaging on all 24 months  or rows of records.

I want to only average based on the prior 6 months of records. 

There are some account with that has less than 6 months of records.. 

proc sql;
create table work.temp2 as
select
a.account_id

,sum(case when yyyymm>="&pre6m." then sales else 0 end) as Pre6m_sales_sum

,avg(case when yyyymm>="202101"  then sales else 0 end) as Pre6m_sales_avg

from work.temp1 a
group by 1
;
run;

[Reeza]

data temp2;

array p{0:5} _temporary_;
set temp1; 
by account_id yyyymm;

if first.account_id then do;
 call missing(of p{*}); 
  counter=0;
end;

counter+1;
p{mod(_n_,6)} = sales;

if counter >=6 then do;
    Pre6m_sales_sum= sum(of p{*});
    Pre6m_sales_avg= mean(of p{*});
end;
else do;
   Pre6m_sales_sum=0;
    Pre6m_sales_avg=0;
end;

run;

Assuming your data is sorted, I prefer temporary arrays. Otherwise, you do need a self join and a method to handle when you have less than 6 occurrences (ie the first 5 months of your data set). That becomes a few CASE statements in SQL.

[superking]

thank you, this type of syntax is foreign to me... 

[PGStats · Accepted solution]

Zero is just another number... if you want to remove some values from the average calculations, you must replace them with missing values:

proc sql;
create table work.temp2 as
select
a.account_id
,sum(case when yyyymm>="&pre6m." then sales else . end) as Pre6m_sales_sum
,avg(case when yyyymm>="202101"  then sales else . end) as Pre6m_sales_avg
from work.temp1 a
group by 1
;

[superking]

Follow up question

I want to sum the prior 12 month sales , however there would be scenarios where there are less than 12 months, then I'd like to annualize the sales  based on the # of months. I do have a record on how many months they have been on book.

Code I have below which does not work.

,sum(case when max(mob)>=12 and yyyymm>="&pre12m." then sum(sales)
                 when max(mob)<12 and yyyymm>="&pre12m." then (sum(sales)/max(mob))*12
                else 0 end) as Pre12m_sales_annualized

[PGStats]

That would be :

, 12 * avg(case when yyyymm>="&pre12m." then sales else . end) as Pre12m_sales_annualized

[superking]

perfect, thank you!