DATA Step, Macro, Functions and more

Lookup -

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 7
Accepted Solution

Lookup -

Hello,

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

E.g.

Order_IDCustomer nameEmailRegular shopWeb-shop
12345

James

James.adams@gmail.com10
54345PhilPil@ah.uk

0

1

84736

JamesJames.adams@gmail.com01
48273RunRunner@anr.eu10

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


Accepted Solutions
Solution
‎11-30-2014 10:22 AM
PROC Star
Posts: 7,474

Re: Lookup -

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;

View solution in original post


All Replies
Trusted Advisor
Posts: 1,228

Re: Lookup -

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;

Solution
‎11-30-2014 10:22 AM
PROC Star
Posts: 7,474

Re: Lookup -

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;

Occasional Contributor
Posts: 7

Re: Lookup -

Thank you for the answers, it was very helpful

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 225 views
  • 3 likes
  • 3 in conversation