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

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

1 ACCEPTED SOLUTION

Accepted Solutions
stat_sas
Ammonite | Level 13

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

5 REPLIES 5
Reeza
Super User

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

klappy711
Calcite | Level 5

I need all the records for those customers

stat_sas
Ammonite | Level 13

Try this.

proc sql;

create table want as

select *  from have

group by customer

having count(distinct region)>1;

quit;

klappy711
Calcite | Level 5

stat@sas,

Perfect! Thank you!

Reeza
Super User

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

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
  • 5 replies
  • 520 views
  • 3 likes
  • 3 in conversation