Hello, I am trying to do this: as long as each customer_ID has records (vertically) with segment meeting the logic below, then we can have a type associated with the customer_ID. For example A1 (in the table below) has different account ID (7 records) and in the 7 records, the segment has P2 and no T so that the A1 is Type A; 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’, 'I') -> 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 I am not sure with two parts: how to write the 5/6 conditions (highlighted in orange) that illustrate the conditions that not meet any of above requirements I am using a case when but that one assigns a type for each row instead of looking at the records in a vertical view and then assign a type for client_ID proc sql; create table flag_testing as select a.customer_ID ,case when segment in ('D','M','P1','P2') and segment ne 'T' then 'Type A' when segment eq 'T' and not in ('D', 'M', 'P1', 'P2') then 'Type B' when segment eq 'I' and not in ('D', 'M', 'P1', 'P2','T') then 'Type C' when segment not in ('D', 'M', 'P1', 'P2','T','I') then 'Other' when segment not in ( )and revene1>0 and revene2>0 then 'Type A' when segment not in ( )and revene1<=0 and revene2<=0 then 'Type B' else ' ' end as type from nodup_sub_testing a group by customer_ID; quit; Customer_ID Account_ID Record_Date Segment Revenue1 Revenue2 A1 001 17-Dec-18 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 A2 004 4-Apr-21 I 0 0 A2 004 18-Oct-21 I 0 0 A3 005 17-Aug-20 0 0 A3 006 19-Oct-20 N 1356 1389 A3 007 17-Dec-18 SS 0 0 A3 008 21-Dec-20 N 0 0 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 T 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 B3 011 25-Jun-20 T 0 0 B3 012 16-Aug-21 D -50 -900 B3 012 18-Oct-21 T -90 -89 desired result: Customer_ID Type A1 Type A A2 Type C A3 Other B1 Type B B2 Type C B3 Type B Thanks a lot.
... View more