Suppose I have two datasets like below: CUSTOMER dataset: customer_id, last_name 001, Natha 002, Cooper 003, David VISIT dataset: customer_id, visit_date 001, 01-MAR-2011 001, 03-APR-2011 001, 15-MAY-2011 002, 21-FEB-2011 I want to use one PROC SQL to produce the visit date count for each customer during APR-2011. Here is my query: proc sql; create table report as select a.customer_id, count(distinct b.visit_date) as visit_cnt from customer a left join visit b on a.customer_id = b.customer_id where b.visit_date between '01apr2011'd and '30apr2011'd group by a.customer_id ; quit; This produces: 001, 1 But the output I want is: 001, 1 002, 0 003, 0 Any suggestion for using one PROC SQL to produce the desired result?
... View more