"Else" statement for PROC SQL when creating table

[edison83]

Hello everyone, i am using the following statement to integrate information from 2 different tables. Table1 with ~20000rows, and Table2 with ~2500rows.

I believe that my code is correct because i am being able to merge the tables. However, the values that don't match the "where" statements are being deleted.

Is there any way to modify my code below to keep the information from Table1, and bring only the information from table 2 that matches the where statement without deleting the data. I mean, is there any way to do that using the else and keep statement.

When i run the following code y table 3 ends up with 10000 rows only. Tha`s OK because i do not expect that all rows match, but I rather prefer to have Table 3 with the 20000 original rows and the 2500 rows from table 2 that matched already included on it.

proc sql;
create Table 3 as select
Table2.Information1, Table2.Information2, Table1.* from Table2, Table1
where Table1.Date >= Table2.Date1 and Table1.Date <= Table2.Date2 and
Table1.ID= Table2.ID;
quit;

I wonder if there is any way to include the "else" "keep" here to fix this issue.

Thanks guys.

[ChrisNZ · Accepted solution]

To get you started:

select Table1.*
     , Table2.Information1
     , Table2.Information2
from Table1
       left join 
     Table2
       on  Table1.Date >= Table2.Date1 
       and Table1.Date <= Table2.Date2 
       and Table1.ID    = Table2.ID    ;

Note how good formatting makes the code so much easier to read and vet.

[edison83]

Thanks Chris, i see now that changing the structure is better.

However, instead of 10000 rows now, table 3 has 13000 Browns...much better than the first one but i still don`t understand why it`s not keeping the original 20000 rows.

I will work more with the data and let you know if i figure out something.

Thanks for the help

[edison83]

Up and running Chris, it`s working perfectly now!

Thanks for the code

[Reeza]

WHERE filters your table by definition. If you do not specify the join type by default it becomes a cross join which is very inefficient. Explicitly specify your join and the fields you want to join on always is better practice in general. ChrisNZ solution changes your join from a cross join to a left join and then controls the join. If the join condition isn't met the rows from the left table are maintained and no fields from the other table are included.