SAS Programming

DATA Step, Macro, Functions and more
BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Mark7
Obsidian | Level 7

Hi All,

 

I am trying calculate rolling 150 days sum and then set a flag  'Y' if the sum equals to 6 or more.

In next column I am trying to  flag 'Y' when previous product was C no matter what was next A, B or C irrelevant is ‘spend’.

In third column important is to set up flag when between two dates is 4 or less days irrelevant is ‘spend’.

 

 Similarly for next id’s as well.

 

Thank you

 

iddateprodspendrolling_spendflag within 150 days months sum equals to 6 or moreif 25 days from previous  C 4 days  between any A, B C
111Dec2017A11   
112Jan2018B23   
123Apr2018B36Y  
131May2018C16Y  
112Jun2018B26YY 
123Jul2018A39Y  
131Jul2018C110Y  
112Aug2018B212YY 
123Aug2018B315YY 
131Aug2018C116Y  
112Nov2018B212Y  
123Dec2018C312Y  
131Dec2018C112YY 
201Mar2018B11   
211Apr2018B12   
221May2018C13   
211Aug2018A24   
221Aug2018C15   
201Sep2018C16YY 
311May2018C11   
312May2018B12 YY
321Jun2018A13   
301Sep2018A14   
311Sep2018C15   
312Oct2018B14   
321Nov2018A14   
301Dec2018C15   

 

I found a similar topic and I managed to do the first column but I have a problem with the next ones

data have ;
input id date : date10. prod $ spend ;
format date date9.;
CARDS;
1 11Dec2017 A 1
1 12Jan2018 B 2
1 23Apr2018 B 3
1 31May2018 C 1
1 12Jun2018 B 2
1 23Jul2018 A 3
1 31Jul2018 C 1
1 12Aug2018 B 2
1 23Aug2018 B 3
1 31Aug2018 C 1
1 12Nov2018 B 2
1 23Dec2018 B 3
1 31Dec2018 C 1
2 01Mar2018 B 1
2 11Apr2018 B 1
2 21May2018 C 1
2 11Aug2018 A 2
2 21Aug2018 C 1
2 01Sep2018 C 1
3 11May2018 C 1
3 12May2018 B 1
3 21Jun2018 A 1
3 01Sep2018 A 1
3 11Sep2018 C 1
3 12Oct2018 B 1
3 21Nov2018 A 1
3 01Dec2018 C 1

;;;;
proc sql;
create table want as
select
t1.id,
t1.date,
t1.spend,
sum(t2.spend) as Rolling_Spend,
case when sum(t2.spend) >=6 then 'Y' end as Flag
from
have t1
left join have t2
on t1.id=t2.id
and intck('day',t2.date,t1.date) between 0 and 150
group by t1.id, t1.date, t1.spend
order by t1.id, t1.date;
quit;

1 ACCEPTED SOLUTION

Accepted Solutions
learsaas
Quartz | Level 8
data want;
	set have;
	attrib rolling_spend length=8;
	attrib FLAG1 length=$1 label='flag within 150 days months sum equals to 6 or more';
	attrib FLAG2 length=$1 label='if 25 days from previous  C';
	attrib FLAG3 length=$1 label='4 days  between any A, B C';
	rolling_spend=0;FLAG1='';FLAG2='';FLAG3='';
	do i=1 to _n_;
		set have(rename=(id=id1 date=date1 prod=prod1 spend=spend1)) point=i; 
		if id=id1 and 0<=date-date1<=150 then rolling_spend+spend1;
		if id=id1 and 0<=date-date1<=25 and prod1='C' and i^=_n_ then flag2='Y';
		if id=id1 and 0<=date-date1<=4 and prod1 in ('A','B','C') and i^=_n_ then flag3='Y';
	end;
	if rolling_spend>=6 then FLAG1='Y';
	drop id1 date1 spend1 prod1 i;
run;

View solution in original post

4 REPLIES 4
PeterClemmensen
Tourmaline | Level 20

Welcome to the SAS communities 🙂

 

Do you have SAS/ETS?

learsaas
Quartz | Level 8
data want;
	set have;
	attrib rolling_spend length=8;
	attrib FLAG1 length=$1 label='flag within 150 days months sum equals to 6 or more';
	attrib FLAG2 length=$1 label='if 25 days from previous  C';
	attrib FLAG3 length=$1 label='4 days  between any A, B C';
	rolling_spend=0;FLAG1='';FLAG2='';FLAG3='';
	do i=1 to _n_;
		set have(rename=(id=id1 date=date1 prod=prod1 spend=spend1)) point=i; 
		if id=id1 and 0<=date-date1<=150 then rolling_spend+spend1;
		if id=id1 and 0<=date-date1<=25 and prod1='C' and i^=_n_ then flag2='Y';
		if id=id1 and 0<=date-date1<=4 and prod1 in ('A','B','C') and i^=_n_ then flag3='Y';
	end;
	if rolling_spend>=6 then FLAG1='Y';
	drop id1 date1 spend1 prod1 i;
run;
Mark7
Obsidian | Level 7

Thank you learsaas.

 

You are very helpful. Everything works great. I owe you so much

Mark7
Obsidian | Level 7

Hi draycut

Yes I do have SAS/ETS

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 1074 views
  • 0 likes
  • 3 in conversation