Subsetting a dataset for customers with multiple sales regions

Accepted Solution Solved
Reply
Contributor
Posts: 26
Accepted Solution

Subsetting a dataset for customers with multiple sales regions

I have a dataset of customer sales with customers and regions. There are only two regions and I'm trying to figure out how to subset my data so I only have customers that have sales in both regions.

My data looks something like this:

CustomerRegionSales
1212
1214
1211
1216
2215
2222
2621
3214
3616
3615
3614
3215
4615
4611
4621
4620

So basically, in this case, I want to know how I can have a subset of this dataset that will only include customers 2 and 3, since they are the only customers with sales in both regions.

I'm using SAS Enterprise Guide, and I don't know if there is a way to do this using the GUI or if I should use a code node

Thanks!le


Accepted Solutions
Solution
‎06-30-2014 05:10 PM
Trusted Advisor
Posts: 1,204

Re: Subsetting a dataset for customers with multiple sales regions

Try this.

proc sql;

create table want as

select *  from have

group by customer

having count(distinct region)>1;

quit;

View solution in original post


All Replies
Super User
Posts: 17,942

Re: Subsetting a dataset for customers with multiple sales regions

Do you need all the records or just the id of customer 2/3?

Contributor
Posts: 26

Re: Subsetting a dataset for customers with multiple sales regions

I need all the records for those customers

Solution
‎06-30-2014 05:10 PM
Trusted Advisor
Posts: 1,204

Re: Subsetting a dataset for customers with multiple sales regions

Try this.

proc sql;

create table want as

select *  from have

group by customer

having count(distinct region)>1;

quit;

Contributor
Posts: 26

Re: Subsetting a dataset for customers with multiple sales regions

stat@sas,

Perfect! Thank you!

Super User
Posts: 17,942

Re: Subsetting a dataset for customers with multiple sales regions

Here's another way with Query Builder. I'd go with code myself Smiley Happy

🔒 This topic is solved and locked.

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

Discussion stats
  • 5 replies
  • 200 views
  • 3 likes
  • 3 in conversation