topic customers who never order anything with data step in SAS Programming

customers who never order anything with data step

ccnky123 — Tue, 11 Sep 2018 06:15:47 GMT

Suppose that a website contains two tables, the Customers table and the Orders table. Use data step to find all customers who never order anything.

Table: Customers.

+----+-------+
| Id | Name  |
+----+-------+
| 1  | Joe   |
| 2  | Henry |
| 3  | Sam   |
| 4  | Max   |
+----+-------+

Table: Orders.

+----+------------+
| Id | CustomerId |
+----+------------+
| 1  | 3          |
| 2  | 1          |
+----+------------+

Using the above tables as example, return the following:

+-----------+
| Customers |
+-----------+
| Henry     |
| Max       |
+-----------+

Re: customers who never order anything with data step

Jagadishkatam — Tue, 11 Sep 2018 06:31:43 GMT

data customer;
input customer_id name$;
cards;
1   Joe   
2   Henry 
3   Sam   
4   Max   
;


data orders;
input id customer_id;
cards;
1   3
2   1
;

proc sql;
create table want as select a.* from customer as a where a.customer_id not in (select customer_id from orders);
quit;

Re: customers who never order anything with data step

PeterClemmensen — Tue, 11 Sep 2018 06:39:37 GMT

do like this

data customer;
input customer_id name$;
cards;
1   Joe   
2   Henry 
3   Sam   
4   Max   
;

data orders;
input id customer_id;
cards;
1   3
2   1
;

proc sort data=customer;by customer_id;run;
proc sort data=orders;by customer_id;run;

data want(keep=name);
   merge customer orders(in=ino);
   by customer_id;
   if not ino;
run;

Re: customers who never order anything with data step

Jagadishkatam — Tue, 11 Sep 2018 06:42:56 GMT

Alternatively by merge step

proc sort data=customer;
by customer_id;
run;

proc sort data=orders;
by customer_id;
run;


data want;
merge customer(in=a) orders(in=b);
by customer_id ;
if a and not b ;
run;