Left Join Count of Records with Proc SQL Producing Duplicate Records

anonymous_user — Fri, 16 Aug 2019 23:48:43 GMT

I have the following two tables that I want to left join using Proc SQL. One is a table of user ID's, the other are all the sales invoices.

What I want is to join the invoices table onto the user ID table so that the count of invoices shows up in an "invoices" column. My problem is that the user ID's become duplicated.

proc sql;
create table full as
select count(b.invoices) as invoices, b.user_id, b.invoice_date, a.user_id from customers a
LEFT JOIN invoice_db b
on a.user_id=b.user_id
where invoice_date>='01JAN2019'd
GROUP BY a.user_id;
quit;

The output looks like

What I need is

Re: Left Join Count of Records with Proc SQL Producing Duplicate Records

SASKiwi — Sat, 17 Aug 2019 01:04:27 GMT

If you only want one row per customer, what invoice date do you want to read - the earliest or latest? If you want the latest this should work:

proc sql;
create table full as
select  a.user_id 
       ,B.*
from customers a
LEFT JOIN 
( select user_id
         ,count(invoices) as invoices
         ,max(invoice_date) as latest_invoice_date
  from invoice_db
  where invoice_date>='01JAN2019'd
  GROUP BY user_id
) as B
on a.user_id=b.user_id
;
quit;

topic Re: Left Join Count of Records with Proc SQL Producing Duplicate Records in New SAS User

Left Join Count of Records with Proc SQL Producing Duplicate Records

Re: Left Join Count of Records with Proc SQL Producing Duplicate Records