BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
stat_sas
Ammonite | Level 13

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

1 ACCEPTED SOLUTION

Accepted Solutions
pradeepalankar
Obsidian | Level 7

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

3 REPLIES 3
pradeepalankar
Obsidian | Level 7

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
Tourmaline | Level 20

Or in SQL:

create table want as

     select order

     from have

     group by order

     having count(*) > 1;

Data never sleeps
pradeepalankar
Obsidian | Level 7

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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