Transactional overlapping

Accepted Solution Solved
Reply
Trusted Advisor
Posts: 1,204
Accepted Solution

Transactional overlapping

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


Accepted Solutions
Solution
‎01-18-2014 10:05 AM
Frequent Contributor
Posts: 106

Re: Transactional overlapping

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;

View solution in original post


All Replies
Solution
‎01-18-2014 10:05 AM
Frequent Contributor
Posts: 106

Re: Transactional overlapping

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;

Super User
Posts: 5,256

Re: Transactional overlapping

Or in SQL:

create table want as

     select order

     from have

     group by order

     having count(*) > 1;

Data never sleeps
Frequent Contributor
Posts: 106

Re: Transactional overlapping

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 wrongSmiley Happy

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;

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 196 views
  • 1 like
  • 3 in conversation