BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
superking
Calcite | Level 5

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;

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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
;
PG

View solution in original post

6 REPLIES 6
Reeza
Super User
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
Calcite | Level 5

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

PGStats
Opal | Level 21

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
;
PG
superking
Calcite | Level 5

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
Opal | Level 21

That would be :

 

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

PG
superking
Calcite | Level 5

perfect, thank you!

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

How to connect to databases in SAS Viya

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.

Discussion stats
  • 6 replies
  • 4392 views
  • 0 likes
  • 3 in conversation