Lookup -

[Jens_v]

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]

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 · Accepted solution]

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]

Thank you for the answers, it was very helpful