topic Re: Subsetting a dataset for customers with multiple sales regions in SAS Programming

Subsetting a dataset for customers with multiple sales regions

klappy711 — Mon, 30 Jun 2014 20:57:09 GMT

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:

Customer	Region	Sales
1	2	12
1	2	14
1	2	11
1	2	16
2	2	15
2	2	22
2	6	21
3	2	14
3	6	16
3	6	15
3	6	14
3	2	15
4	6	15
4	6	11
4	6	21
4	6	20

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

Re: Subsetting a dataset for customers with multiple sales regions

Reeza — Mon, 30 Jun 2014 21:08:10 GMT

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

Re: Subsetting a dataset for customers with multiple sales regions

klappy711 — Mon, 30 Jun 2014 21:08:40 GMT

I need all the records for those customers

Re: Subsetting a dataset for customers with multiple sales regions

stat_sas — Mon, 30 Jun 2014 21:10:10 GMT

Try this.

proc sql;

create table want as

select * from have

group by customer

having count(distinct region)>1;

quit;

Re: Subsetting a dataset for customers with multiple sales regions

klappy711 — Mon, 30 Jun 2014 21:14:28 GMT

stat@sas,

Perfect! Thank you!

Re: Subsetting a dataset for customers with multiple sales regions

Reeza — Mon, 30 Jun 2014 21:17:25 GMT

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

Customer	Region	Sales
1	2	12
1	2	14
1	2	11
1	2	16
2	2	15
2	2	22
2	6	21
3	2	14
3	6	16
3	6	15
3	6	14
3	2	15
4	6	15
4	6	11
4	6	21
4	6	20

Customer	Region	Sales
1	2	12
1	2	14
1	2	11
1	2	16
2	2	15
2	2	22
2	6	21
3	2	14
3	6	16
3	6	15
3	6	14
3	2	15
4	6	15
4	6	11
4	6	21
4	6	20

Customer	Region	Sales
1	2	12
1	2	14
1	2	11
1	2	16
2	2	15
2	2	22
2	6	21
3	2	14
3	6	16
3	6	15
3	6	14
3	2	15
4	6	15
4	6	11
4	6	21
4	6	20