CODEWITHSUNDEEP
sqljoinsas
user2121517
user2121517

Reputation: 11

Joining two tables in SAS with SQL

I'm trying to join two tables, table1 and table2. Along with other variables, both tables have two variables, COMPANY and DATE, the combination of which uniquely identifies every observation in a given table. I want to join the two tables on COMPANY and a variable I create called DATEDIF, which is the absolute difference in the DATE variable found in table1 and the DATE variable found in table 2. If I find a match on COMPANY, I only want to keep the match if DATEDIF is less than 100. I want to keep every observation from table1, regardless of whether I find a match for it, so I am doing a left join. Here is the code I have:

proc sql; create table mergedtable as select
a.*, b.*, abs(a.date-b.date) as datedif
from table1 a left join table2 b
on.company=b.company
group by a.company, a.date
having datedif<100;
quit;

The problem is, when I do this join, mergedtable only has 2229 observations even though table1 has 2705 observations. Since I'm doing a left join on table1, I thought mergedtable should have at least 2705 observations, possibly more if more than one observation from table2 meets the matching criteria detailed above.

Where am I going wrong?

Upvotes: 1

Views: 448

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270873

I think this is what you want:

proc sql;
    create table mergedtable as
        select a.*, b.*, abs(a.date-b.date) as datedif
        from table1 a left join
             table2 b
             on.company = b.company and abs(a.date-b.date) < 100
        group by a.company, a.date;

Using * with group by is not recommended.

Upvotes: 1

Related Questions