topic Re: How to design the code with multiple conditions? in SAS Enterprise Guide

How to design the code with multiple conditions?

y3arire_7 — Fri, 26 Apr 2019 05:09:54 GMT

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" :

( if the count of the value in "Balance" more than 0 ) greater than or equal to 6 in the past 12 months then the "Desired Type" is "B" else "G"

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

Re: How to design the code with multiple conditions?

AMSAS — Fri, 26 Apr 2019 12:46:29 GMT

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

Re: How to design the code with multiple conditions?

PeterClemmensen — Mon, 29 Apr 2019 21:37:52 GMT

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