topic Re: Lookup - in SAS Programming

Lookup -

Jens_v — Sun, 30 Nov 2014 10:21:19 GMT

Hello,

Is it possible to make a SAS code to lookup if a customer have bought from multiple channels.

E.g.

Order_ID	Customer name	Email	Regular shop	Web-shop
12345	James	James.adams@gmail.com	1	0
54345	Phil	Pil@ah.uk	0	1
84736	James	James.adams@gmail.com	0	1
48273	Run	Runner@anr.eu	1	0

Therefore, I want to check it if a customer (by Email), at any given time, has bought in the web-shop, also bought in the regular shop.

If this is the case. this customer should have a "1" in a new column.

In the above mentioned example James would get a 1 in the new column because he bought in both shops.

I hope you guys can help me.

Kind regards

Jens

stat_sas — Sun, 30 Nov 2014 13:59:34 GMT

Hi,

Try this.

proc sql;

create table want as

select a.*,b.new_var from have a

left join

(select Customer_name, Email, case when sum(Regularshop)=1 and sum(Webshop)=1 then 1 else 0

end as new_var

from have

group by Customer_name, Email) b

on a.email=b.email;

quit;

art297 — Sun, 30 Nov 2014 15:22:58 GMT

How about?:

proc sql;

create table want as

select *,

case when sum(Regular_shop) ge 1 and sum(Web_shop) ge 1 then 1

else 0

end as new_var

from have

group by Email

;

quit;

Jens_v — Sun, 30 Nov 2014 16:57:54 GMT

Thank you for the answers, it was very helpful