Left join using proc sql

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

Left join using proc sql

Hello,

 

I am trying to merge two datasets according to a unique customer IDs (NO_CUST) and date (DATE). My data consists of usage data on TV (table 1) and web applications (table 2) and I want to merge customer usage from both tables only for those in table 1 and drop any users from table 2 that are not in table 1. 

 

I am running a LEFT join using PROC SQL, however, my original t1 sample has 30k unique IDs, and once the tables are merged, I am loosing 20k of those users although I want to keep them and lose those in t2.

 

Here is the code, any idea what is wrong here ?

 

 

PROC SQL;
CREATE TABLE LIB.USAGE_TV_WEB AS
SELECT   t1.NO_CUST,
                 t1.DATE,
                 t1.DUREE_LIN_TV,
                 t1.DUREE_ENP_TV,
                 t1.DUREE_VSDP_TV,
                 t1.DUREE_VSDA_TV,
                 t1.DUREE_VSDG_TV,
                 t2.DUREE_VSDG_Web,
                 t2.DUREE_VSDA_Web,
                 t2.DUREE_VSDP_Web,
                 t2.DUREE_LIN_Web
FROM LIB.MERGE_TV t1
LEFT JOIN LIB.MERGE_WEB t2 ON (t1.NO_CUST = t2.NO_CUST) AND (t1.DATE=t2.DATE);

QUIT;

 


Accepted Solutions
Solution
‎10-25-2015 11:10 PM
Respected Advisor
Posts: 4,937

Re: Left join using proc sql

Posted in reply to marcgosselin

A LEFT JOIN B keeps all the lines from A. Some of them are duplicated when many lines from B match, but none is dropped. 

PG

View solution in original post


All Replies
Respected Advisor
Posts: 4,937

Re: Left join using proc sql

Posted in reply to marcgosselin

Puzzling... Make sure you are looking at the right version of your tables. After running your query, try

 

PROC SQL;
SELECT COUNT(*) AS N_TV FROM LIB.MERGE_TV;
SELECT COUNT(*) AS N_WEB FROM LIB.MERGE_WEB;
SELECT COUNT(*) AS N_USAGE FROM LIB.USAGE_TV_WEB;
QUIT;

 

N_USAGE should be >= N_TV

PG
New Contributor
Posts: 4

Re: Left join using proc sql

[ Edited ]

Hello PG,

 

Thank you so much for your reply. 

 

Once running the sql above, and / or filtering for missing values in the customer account # (NO_CLI), N_USAGE = N_TV

 

Is there a reason why the LEFT JOIN SQL keeps these missing values in my original outout ?

 

Thanks !

 

Marc

Regular Contributor
Posts: 161

Re: Left join using proc sql

Posted in reply to marcgosselin

Just a suggestion..... 

 

Have you considered merging using a SAS MERGE?   Not that it should matter but it will be easy to view the counts from the logs... not so much with the SQL...  If you are able to get the counts that PG recommended, it might bring to light if there is anything missing.   The SQL looks good to me... can't spot anything....

Kannan Deivasigamani
New Contributor
Posts: 4

Re: Left join using proc sql

Thanks for the help Kannan, the counts did indeed shed some light on the issue.

 

Marc.

Solution
‎10-25-2015 11:10 PM
Respected Advisor
Posts: 4,937

Re: Left join using proc sql

Posted in reply to marcgosselin

A LEFT JOIN B keeps all the lines from A. Some of them are duplicated when many lines from B match, but none is dropped. 

PG
New Contributor
Posts: 4

Re: Left join using proc sql

Thank you very much PG, with the counts and your latest reply, the issue seems to be solved.

 

I drop all missing values in a seperate SQL and I have the same counts between my original TV table and the final merge with my web table.

 

Thank you very much for your help and quick reply !

 

 

🔒 This topic is solved and locked.

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

Discussion stats
  • 6 replies
  • 442 views
  • 0 likes
  • 3 in conversation