topic Compare observations within groups in SAS Procedures

Compare observations within groups

HarryLiu — Thu, 29 Oct 2015 14:19:38 GMT

Hi SAS Users,

I have a road block in conduct a project. The problem is how to compare observations within groups. My data set has a column named STORE_ID, which is the ID number of each store in a city. Another column is customer_id, obveriously it is ID of each consumed customer. I want to know the list of Customer_id that consumed not only in store A, but also in store B. I think correlated sub-query might be a possible solution, but don't know how to develop code. Any suggestions and sample codes are highly appreciated.

Thanks,

Harry

Re: Compare observations within groups

LinusH — Thu, 29 Oct 2015 14:37:43 GMT

I think that a group by with a having count(*) =2 clause would work, given that the store and customer the combined key.

Re: Compare observations within groups

Reeza — Thu, 29 Oct 2015 14:48:30 GMT

Do you only have two stores or multiple stores?

Re: Compare observations within groups

RW9 — Thu, 29 Oct 2015 14:51:13 GMT

Post some test data (in the form of a datastep) and required output.

Re: Compare observations within groups

Astounding — Thu, 29 Oct 2015 14:56:43 GMT

If you are only concerned with those two stores, here's a way:

proc sort data=have;

by customer_id;

run;

data a_only b_only both;

merge have (where=(store_id='A') in=shopped_at_a)

have (where=(store_id='B') in=shopped_at_b);

by customer_id;

if shopped_at_a=0 then output b_only;

else if shopped_at_b=0 then output a_only;

else output both;

run;

Note that this doesn't account for the possibility that a customer shopped more than one time at a store. There are ways to handle that, but we'd need a better picture of what is in your data and what you are trying to achieve.

Good luck.

Re: Compare observations within groups

HarryLiu — Thu, 29 Oct 2015 15:12:16 GMT

Thanks so much for everybody's suggestion. I have hundreds of stores and need to compare any interested stores (usually less than 4 stores). Attached is a sample of data set. Astounding's code looks good, but will be time consuming, since I have hundred stores.
Here is my data set,
Store_id Customer_ID
CAL 11542
CAL 11526
CHI 10069
CHI 11433
CHI 11542
GFC 11493
GFC 11526
GFC 10068
KAN 11482
KAN 11493

If I compare CAL with CHI, the result should be 11542. IF between CAL and GFC, the result should be 11526. If between GFC and KAN, the result should be 11493. If between CAL and KAN, result should be nothing.
Thanks,
Harry

Re: Compare observations within groups

MikeZdeb — Thu, 29 Oct 2015 15:25:14 GMT

Hi, how about INTERSECT in SQL (works for 2 stores , works for 'n' stores) ...

data x;
input store_id :$1. customer_id @@;
datalines;
A 123 B 234 A 789 B 123 A 999 A 234 B 765 C 123
;

title 'CUSTOMERS COMMON TO STORES A & B';
proc sql;
select customer_id from x where store_id = 'A'
intersect
select customer_id from x where store_id = 'B';
quit;

title 'CUSTOMERS COMMON TO STORES A & B & C;
proc sql;
select customer_id from x where store_id = 'A'
intersect
select customer_id from x where store_id = 'B'
intersect
select customer_id from x where store_id = 'C';
quit;

CUSTOMERS COMMON TO STORES A & B

customer_id
123
234

CUSTOMERS COMMON TO STORES A & B & C

customer_id
123

Re: Compare observations within groups

stat_sas — Thu, 29 Oct 2015 15:58:23 GMT

Try this.

data have;
input Store_id $ Customer_ID;
datalines;
CAL 11542
CAL 11526
CHI 10069
CHI 11433
CHI 11542
GFC 11493
GFC 11526
GFC 10068
KAN 11482
KAN 11493
;

proc sql;
create table store as
select * from have group by customer_id
having count(distinct store_id)>1;
quit;

proc transpose data=store out=want(drop=_Name_);
by customer_id;
var store_id;
run;

proc print data=want;
run;

Re: Compare observations within groups

Astounding — Thu, 29 Oct 2015 16:07:51 GMT

A more general approach might be just what you need.

proc sort data=have (keep=customer_id store_id) out=full_list nodupkey;

by customer_id store_id;

run;

data full_list;

set full_list;

shopped_there='Y';

run;

proc transpose data=full_list out=want;

by customer_id;

id store_id;

var shopped_there;

run;

It assumes that the store names form valid variable names in SAS, and gives you a "Y" for every store where a customer shopped. You can use that for any combination of subsetting going forward.

Re: Compare observations within groups

HarryLiu — Thu, 29 Oct 2015 18:36:05 GMT

Thanks everybody for various suggestions and helps. I found all code work very well for my project but this one is the most efficient. The only reason is that my data set is complex and there will be tons of comparison.
I appreciate everybody's time.
Thanks,
Harry