topic Re: SQL AVG Function Case when on selective rows in SAS Data Management

SQL AVG Function Case when on selective rows

superking — Thu, 16 Jun 2022 17:31:29 GMT

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;

Re: SQL AVG Function Case when on selective rows

Reeza — Thu, 16 Jun 2022 17:42:26 GMT

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.

Re: SQL AVG Function Case when on selective rows

PGStats — Thu, 16 Jun 2022 18:58:10 GMT

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
;

Re: SQL AVG Function Case when on selective rows

superking — Fri, 17 Jun 2022 00:32:43 GMT

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

Re: SQL AVG Function Case when on selective rows

superking — Fri, 17 Jun 2022 00:42:47 GMT

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

Re: SQL AVG Function Case when on selective rows

PGStats — Fri, 17 Jun 2022 00:59:41 GMT

That would be :

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

Re: SQL AVG Function Case when on selective rows

superking — Fri, 17 Jun 2022 01:38:05 GMT

perfect, thank you!