topic Re: Transactional overlapping in SAS Programming

Transactional overlapping

stat_sas — Sat, 18 Jan 2014 14:39:31 GMT

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

Re: Transactional overlapping

pradeepalankar — Sat, 18 Jan 2014 15:05:38 GMT

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;

Re: Transactional overlapping

LinusH — Sat, 18 Jan 2014 16:04:29 GMT

Or in SQL:

create table want as

select order

from have

group by order

having count(*) > 1;

Re: Transactional overlapping

pradeepalankar — Sat, 18 Jan 2014 17:06:23 GMT

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;