Hihi
May I know how to design the code with multiple conditions?
1) The result for "Desired Type" needs to be by "Customer ID".
2) There are few conditions to produce "Desired Type" :
On top of that, could anyone share the opinion how to understand or simplify the concept to design the code when encountering the multiple or nested conditions?
Thank you so much
Period | Customer ID | Balance | Desired Type |
201304 | 1 | 0 | G |
201305 | 1 | 0 | G |
201306 | 1 | 120 | G |
201307 | 1 | 0 | G |
201308 | 1 | 130 | G |
201309 | 1 | 50 | G |
201310 | 1 | 80 | G |
201311 | 1 | 0 | G |
201312 | 1 | 0 | G |
201401 | 1 | 50 | G |
201402 | 1 | 70 | B |
201403 | 1 | 0 | B |
201404 | 1 | 0 | B |
201405 | 1 | 0 | B |
201406 | 1 | 0 | G |
201407 | 1 | 50 | B |
201408 | 1 | 30 | B |
201409 | 1 | 80 | B |
201410 | 1 | 0 | G |
201411 | 1 | 0 | G |
201412 | 1 | 0 | G |
201304 | 8 | 0 | G |
201305 | 8 | 0 | G |
201306 | 8 | 0 | G |
201307 | 8 | 0 | G |
201308 | 8 | 0 | G |
201309 | 8 | 0 | G |
201310 | 8 | 0 | G |
201311 | 8 | 0 | G |
201312 | 8 | 0 | G |
201401 | 8 | 0 | G |
201402 | 8 | 0 | G |
201403 | 8 | 0 | G |
201404 | 8 | 70 | G |
201405 | 8 | 80 | G |
201406 | 8 | 0 | G |
201407 | 8 | 0 | G |
201408 | 8 | 50 | G |
201409 | 8 | 60 | G |
201410 | 8 | 70 | G |
201411 | 8 | 90 | B |
201412 | 8 | 30 | B |
@y3arire_7 if I understand your question correctly you are going to need to make use of the by group processing, retain statement and lagn function (see links to documentation)
Below is an example using your sample data, I first load your data along with your desired result (want_type), then calculate the type, and check it against your desired value setting correct to YES|NO
/* Load test data */
data have ;
format date yymmn6. ;
input date : yymmn6. cid $ balance $ want_type $ ;
cards;
201304 1 0 G
201305 1 0 G
201306 1 120 G
201307 1 0 G
201308 1 130 G
201309 1 50 G
201310 1 80 G
201311 1 0 G
201312 1 0 G
201401 1 50 G
201402 1 70 B
201403 1 0 B
201404 1 0 B
201405 1 0 B
201406 1 0 G
201407 1 50 B
201408 1 30 B
201409 1 80 B
201410 1 0 G
201411 1 0 G
201412 1 0 G
201304 8 0 G
201305 8 0 G
201306 8 0 G
201307 8 0 G
201308 8 0 G
201309 8 0 G
201310 8 0 G
201311 8 0 G
201312 8 0 G
201401 8 0 G
201402 8 0 G
201403 8 0 G
201404 8 70 G
201405 8 80 G
201406 8 0 G
201407 8 0 G
201408 8 50 G
201409 8 60 G
201410 8 70 G
201411 8 90 B
201412 8 30 B
;
run ;
/* Sort data by cid & date */
proc sort data=have ;
by cid date ;
run ;
data want ;
/* Retain a counter, keeps the value of count over itterations of the data step */
retain count 0 ;
/* Read input data */
set have ;
by cid date ;
/* If this is the first observation for a customer ID then reset count to 0 */
if first.cid then
count=0 ;
/* if there is a +ve balance add 1 to counter */
if balance>0 then
count=count+1 ;
/* if the 12th prior balance is +ve and count is +ve then subtract 1 from count */
check=lag12(balance) ;
if count>0 and lag12(balance)>0 then
do ;
count=count-1 ;
end ;
/* if count>=6 set type to B else G */
if count>=6 then
type="B" ;
else
type="G" ;
/* compare the calculated type to test data want_type */
if type=want_type then
correct="YES" ;
else
correct="NO" ;
run ;
@y3arire_7, I'm guessing that your two threads are related 🙂
You can expand on my code given in your thread here. However, for a problem like this, I would use PROC SQL and do something like this. @y3arire_7 , did this work for you? 🙂
data have ;
format Period yymmn6. ;
input Period :yymmn6. Customer_ID Balance;
cards;
201304 1 0
201305 1 0
201306 1 120
201307 1 0
201308 1 130
201309 1 50
201310 1 80
201311 1 0
201312 1 0
201401 1 50
201402 1 70
201403 1 0
201404 1 0
201405 1 0
201406 1 0
201407 1 50
201408 1 30
201409 1 80
201410 1 0
201411 1 0
201412 1 0
201304 8 0
201305 8 0
201306 8 0
201307 8 0
201308 8 0
201309 8 0
201310 8 0
201311 8 0
201312 8 0
201401 8 0
201402 8 0
201403 8 0
201404 8 70
201405 8 80
201406 8 0
201407 8 0
201408 8 50
201409 8 60
201410 8 70
201411 8 90
201412 8 30
;
run;
proc sql;
create table want as
select *,
(select count(*) from have
where Customer_ID=a.Customer_ID
and Balance>0
and (intnx('month', a.Period, -11, 's') le Period le a.Period)) as count,
(case when calculated count ge 6 then 'B'
else 'G' end) as Desired_Type
from have as a;
quit;
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 25. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.