Transactional overlapping

[stat_sas]

Hi Everyone,

I've a table that contains two variables (order and brand). Variable brand represents six different brand names.Some of the orders are overlapping and are tagged to multiple brands. I am looking for solution that can identify an order which is tagged to multiple brands.

Thanks in advance for your expert advise.

Please see below format of my data

order    brand

12        brand1

1          brand1

2          brand2

12        brand2

1          brand3

34        brand4

2          brand5

54        brand6

24        brand1

54        brand1

54        brand2

[pradeepalankar · Accepted solution]

Hi,

please try this:

data test;

input order   brand $;

cards;

12 brand1

1  brand1

2  brand2

12 brand2

1  brand3

34 brand4

2  brand5

54 brand6

24 brand1

54 brand1

54 brand2

;

proc sort data =test nodupkey;

by order brand;

data test(keep=order count);

set test;

by order brand ;

if first.order and first.brand then count=1;

else count+1;

if count>1 and last.order then output;

run;

[LinusH]

Or in SQL:

create table want as

     select order

     from have

     group by order

     having count(*) > 1;

[pradeepalankar]

Hi ,

above sql will not take of different brands associated with order,as per requirement, if i have understood it correctly., please correct me if i am wrong:)

Below sql will take care of that:

proc sql;

select order,count(*) as count from (select distinct order,brand from test)

group by order

having count(*)>1;

quit;