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;
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
;
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.
thank you, this type of syntax is foreign to me...
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
;
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
That would be :
, 12 * avg(case when yyyymm>="&pre12m." then sales else . end) as Pre12m_sales_annualized
perfect, thank you!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.