proc sql link table

[Songchan]

Hello there,

I want to combine two data sets temp03 and temp04 using proc sql select, but it turns out that data set temp04 in my table link is empty.

what's the possible reason of that problem?

proc sql;
create table link as
select distinct temp03.*,temp04.*
from temp03 as a
left join temp04(where=(linktype in ('LC' 'LU') and
linkprim in ('P' 'C' 'J'))) as b

on a.permno=b.lpermno and b.linkdt<a.date;
quit;

[Reeza]

Remove your WHERE conditions and add them one at a time to see if one is returning an empty table.

---

@Songchan wrote:

Hello there,

I want to combine two data sets temp03 and temp04 using proc sql select, but it turns out that data set temp04 in my table link is empty.

what's the possible reason of that problem?

proc sql;
create table link as
select distinct temp03.*,temp04.*
from temp03 as a
left join temp04(where=(linktype in ('LC' 'LU') and
linkprim in ('P' 'C' 'J'))) as b

on a.permno=b.lpermno and b.linkdt<a.date;
quit;

---

[Songchan]

No, still doesn't work

[VDD]

I have not seen the data but would this work:

proc sql;
create table link as
select distinct a.*, b.*
from temp03 as a
left join temp04(where=(upcase(linktype) in ('LC' 'LU') and
upcase(linkprim) in ('P' 'C' 'J'))) as b

on a.permno=b.lpermno and b.linkdt<a.date;
quit;

[Songchan]

Thanks for replying,

but it still doesn't work

[VDD]

is it posable that b.linkdt <= a.date ?

[Songchan]

No, still empty for data set temp04

[PGStats · Accepted solution]

The condition b.linkdt<a.date might be a comparison between a datetime value (b.linkdt) and a date value (a.date). try changing to

datepart(b.linkdt) < a.date

[Songchan]

Thank you PG! it works!