Merging on Multiple Variables in Proc SQL

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 101
Accepted Solution

Merging on Multiple Variables in Proc SQL

Hello All,

I am trying to merge data based on two variables.  I need to join data when both the CUSIP and the QTR is the same.  I am doing something like the following:

proc sql;

  create table hold9 as

  select t1.*,

  t2.SHRSDT label = 'SHARES OBSERVATION DATE',

  t2.SHROUT label = 'SHARES OUTSTANDING'

  from hold7 as t1

  inner join hold8 as t2 on (t1.CUSIP = t2.CUSIP) & (t1.QTR = t2.QTR)

  order by QTR, QTRFLOW;

quit;

But this is giving me a table with 0 observations and 14 (the correct number) of variables. 

I am hoping this is an easy fix.  If not, I will post data and stuff so that you all can work on it more.

Thanks,

John


Accepted Solutions
Solution
‎03-09-2014 02:36 PM
Respected Advisor
Posts: 4,919

Re: Merging on Multiple Variables in Proc SQL

Posted in reply to mahler_ji

If t1.QTR and t2.QTR both have format names starting with YYQ then both underlying values can be different dates within the same quarter and display as the same value. To equate dates from the same quarter, you should replace

 

(t1.QTR = t2.QTR)

 

with

 

intck("QTR", t1.QTR, t2.QTR) = 0

 

PG

PG

View solution in original post


All Replies
Super User
Posts: 19,770

Re: Merging on Multiple Variables in Proc SQL

Posted in reply to mahler_ji

Try changing the & to And and you don't need the brackets.

If you get 0 obs check the format/length of your variables. Sometimes, especially in character variables you'll have trailing spaces in one that won't let the data match.

Super User
Super User
Posts: 7,039

Re: Merging on Multiple Variables in Proc SQL

Posted in reply to mahler_ji

Sounds like there is no overlap between the two tables. Inner join will only take those that are in both tables.

If it looks like there should be an overlap then check the formats on the variables. Perhaps the two QTR variables are not using the same values? Or perhaps they are really dates with a format attached.  Then if the actual dates do not match then the equality test will fail. You could use INTNX() function to adjust the dates.

intnx('qtr',t1.QTR,0)  = intnx('qtr',t2.QTR,0)

Frequent Contributor
Posts: 83

Re: Merging on Multiple Variables in Proc SQL

Posted in reply to mahler_ji

Since you are looking for an inner join you also could have written it as

from hold7 t1, hold8 t2

where t1.CUSIP = t2.CUSIP and t1.QTR = t2.QTR

I find this is the best way if you were using 3 tables and you wanted all 3 to relate to each other. But I agree with Tom and Rezza that there is probably something going on with the formats of you columns that is causing your output to be 0 rows.

Solution
‎03-09-2014 02:36 PM
Respected Advisor
Posts: 4,919

Re: Merging on Multiple Variables in Proc SQL

Posted in reply to mahler_ji

If t1.QTR and t2.QTR both have format names starting with YYQ then both underlying values can be different dates within the same quarter and display as the same value. To equate dates from the same quarter, you should replace

 

(t1.QTR = t2.QTR)

 

with

 

intck("QTR", t1.QTR, t2.QTR) = 0

 

PG

PG
Frequent Contributor
Posts: 101

Re: Merging on Multiple Variables in Proc SQL

Posted in reply to mahler_ji

, , ,

Thank you very much for your help. It turns out that I was not formatting a previous intnx() command with the correct date format, so there was no overlap between the two tables.  I have also changed the way that I am constraining the join in PROC SQL per PGStats' instructions to make sure that I am not getting any mistakes.

Thanks so much,

John

🔒 This topic is solved and locked.

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

Discussion stats
  • 5 replies
  • 2138 views
  • 0 likes
  • 5 in conversation