topic Re: Case when for different conditions in SAS Programming

Case when for different conditions

septemberbulb — Mon, 06 Jun 2022 23:30:57 GMT

Hello, I am trying to apply the following conditions:

but for the fifth and sixth conditions, for the situation does not meet any above requirements, I am not quite sure how to deal with that :

I am trying to add a type for each unique client ID

if a client ID has records with segment in ('D', 'M', 'P2', 'P1') and not equal to ‘T’-> Type A
if a client ID has records with segment equal to ‘T’ and none in ('D', 'M', 'P1', 'P2') -> Type B
if a client ID has records with segment equal to 'I' and none in ('D', 'M', 'P1', 'P2',’T’) -> Type C
if a client ID has no records with segment in ('D', 'M', 'P2', 'P1',’T’) -> Other
if a client ID does not meet any of above requirements but revenue1 >0 and revenue2>0 ->Type A
if a client ID does not meet any of above requirements but has revenue1 <=0 and revenue2<=0 -> Type B

Customer_ID	Account_ID	Record_Date	Segment	Revenue1	Revenue2
A1	001	17-Dec-18	N	0	0
A1	001	17-Apr-19	N	0	0
A1	001	17-Aug-19	N	0	0
A1	001			0	0
A1	002	17-Apr-19	N	0	5000
A1	002	17-Feb-19	M	10000	4000
A1	003	1-Jan-20	P2	4010	3000
A1	003	1-Feb-20	N	4600	4444
A1	003	2-Feb-20	P2	4330	3509
A1	003	3-Feb-20	N	5000	5890
A2	004	4-Apr-21	I	0	0
A2	004	18-Oct-21	I	0	0
A2	005	17-Aug-20		0	0
A2	006	17-Aug-20	P2	1345	667
A2	006	19-Oct-20	N	1356	1389
A2	007	17-Dec-18	SS	0	0
A2	008	19-Aug-19	T	0	0
A2	008	21-Oct-19	I	0	0
A2	008	21-Dec-20	N	0	0
A2	008	19-Apr-21	P2	999	400
B1	009	15-Apr-19	N	8997	7689
B1	009	19-Aug-19	N	758	456
B1	009	21-Oct-19	N	-869	-467
B1	009	16-Dec-19	SS	0	0
B1	009	19-Oct-20	N	0	0
B1	009	21-Dec-20	N	0	0
B1	010	18-Apr-18	I	0	0
B1	010	15-Oct-18	I	0	0
B1	010	17-Jun-19	N	0	0
B2	011	16-Oct-17	I	-97	-97
B2	011	18-Dec-17	N	0	-98
B2	011	19-Feb-18	Q	0	0
B2	011	21-Apr-20	Q	0	0
B2	011	25-Jun-20	Q	0	0
B2	011	16-Oct-17	Q	0	0
B2	011	18-Apr-18	Q	0	0
B2	011	15-Apr-19	T	0	0
B2	011	25-Jun-20	T	0	0
B2	011	17-Aug-20	T	0	0
B2	012	21-Jun-21	T	0	0
B2	012	16-Aug-21	D	47	-900
B2	012	18-Oct-21	T	-90	-89
B2	012	20-Dec-21	T	0	-78
B2	012	19-Oct-20	N	13567	890
B2	013	17-Dec-18	SS	-150	50
B2	013	18-Feb-19	T	0	34909
B2	014	16-Dec-19	T	-1	56786
B2	014	17-Feb-20	T	-1	23
B2	014	25-Jun-20	I	-1456	-456
B2	014	17-Aug-20	I	-2456	-4738

Thank you

Re: Case when for different conditions

Reeza — Tue, 07 Jun 2022 01:21:58 GMT

Is a client identified by a combination of CustomerID and AccountID?

I'm guessing that it's actually an order of operation issues

CASE goes in order of conditions, you may need to move your fourth condition to the last condition or it will catch the last two condition.

This order:

if a client ID has records with segment in ('D', 'M', 'P2', 'P1') and not equal to ‘T’-> Type A
if a client ID has records with segment equal to ‘T’ and none in ('D', 'M', 'P1', 'P2') -> Type B
if a client ID has records with segment equal to 'I' and none in ('D', 'M', 'P1', 'P2',’T’) -> Type C
if a client ID does not meet any of above requirements but revenue1 >0 and revenue2>0 ->Type A
if a client ID does not meet any of above requirements but has revenue1 <=0 and revenue2<=0 -> Type B
if a client ID has no records with segment in ('D', 'M', 'P2', 'P1',’T’) -> Other

If something like that doesn't work, please post what the Type is for each of your codes shown and include the code you have so far.

@septemberbulb wrote:

Hello, I am trying to apply the following conditions:

but for the fifth and sixth conditions, for the situation does not meet any above requirements, I am not quite sure how to deal with that :

I am trying to add a type for each unique client ID

if a client ID has records with segment in ('D', 'M', 'P2', 'P1') and not equal to ‘T’-> Type A

if a client ID has records with segment equal to ‘T’ and none in ('D', 'M', 'P1', 'P2') -> Type B

if a client ID has records with segment equal to 'I' and none in ('D', 'M', 'P1', 'P2',’T’) -> Type C

if a client ID has no records with segment in ('D', 'M', 'P2', 'P1',’T’) -> Other

if a client ID does not meet any of above requirements but revenue1 >0 and revenue2>0 ->Type A

if a client ID does not meet any of above requirements but has revenue1 <=0 and revenue2<=0 -> Type B

Customer_ID Account_ID Record_Date Segment Revenue1 Revenue2

A1 001 17-Dec-18 N 0 0

A1 001 17-Apr-19 N 0 0

A1 001 17-Aug-19 N 0 0

A1 001 0 0

A1 002 17-Apr-19 N 0 5000

A1 002 17-Feb-19 M 10000 4000

A1 003 1-Jan-20 P2 4010 3000

A1 003 1-Feb-20 N 4600 4444

A1 003 2-Feb-20 P2 4330 3509

A1 003 3-Feb-20 N 5000 5890

A2 004 4-Apr-21 I 0 0

A2 004 18-Oct-21 I 0 0

A2 005 17-Aug-20 0 0

A2 006 17-Aug-20 P2 1345 667

A2 006 19-Oct-20 N 1356 1389

A2 007 17-Dec-18 SS 0 0

A2 008 19-Aug-19 T 0 0

A2 008 21-Oct-19 I 0 0

A2 008 21-Dec-20 N 0 0

A2 008 19-Apr-21 P2 999 400

B1 009 15-Apr-19 N 8997 7689

B1 009 19-Aug-19 N 758 456

B1 009 21-Oct-19 N -869 -467

B1 009 16-Dec-19 SS 0 0

B1 009 19-Oct-20 N 0 0

B1 009 21-Dec-20 N 0 0

B1 010 18-Apr-18 I 0 0

B1 010 15-Oct-18 I 0 0

B1 010 17-Jun-19 N 0 0

B2 011 16-Oct-17 I -97 -97

B2 011 18-Dec-17 N 0 -98

B2 011 19-Feb-18 Q 0 0

B2 011 21-Apr-20 Q 0 0

B2 011 25-Jun-20 Q 0 0

B2 011 16-Oct-17 Q 0 0

B2 011 18-Apr-18 Q 0 0

B2 011 15-Apr-19 T 0 0

B2 011 25-Jun-20 T 0 0

B2 011 17-Aug-20 T 0 0

B2 012 21-Jun-21 T 0 0

B2 012 16-Aug-21 D 47 -900

B2 012 18-Oct-21 T -90 -89

B2 012 20-Dec-21 T 0 -78

B2 012 19-Oct-20 N 13567 890

B2 013 17-Dec-18 SS -150 50

B2 013 18-Feb-19 T 0 34909

B2 014 16-Dec-19 T -1 56786

B2 014 17-Feb-20 T -1 23

B2 014 25-Jun-20 I -1456 -456

B2 014 17-Aug-20 I -2456 -4738

Thank you

Re: Case when for different conditions

septemberbulb — Tue, 07 Jun 2022 02:57:36 GMT

For example, for client ID A, it has multiple records as long as any records meet the condition, based on the logic, the result will mark the client ID A is Type A. But when I use case when, the result only helps me generate a type after each row. it is not a aggerated view for each Client. Group by Client.

Re: Case when for different conditions

Kurt_Bremser — Tue, 07 Jun 2022 07:00:50 GMT

I see no value "A" anywhere in your data. How is a "client" defined? Solely by customer_id, or as a combination of customer_id and account_id?

Re: Case when for different conditions

septemberbulb — Tue, 07 Jun 2022 12:56:41 GMT

Sorry Let me repost this for a better view.

Re: Case when for different conditions

Reeza — Tue, 07 Jun 2022 15:16:43 GMT

Show your code.

Your code is very likely wrong but without seeing it, can't say how.