topic Re: proc sql link table in SAS Programming

proc sql link table

Songchan — Sun, 16 Sep 2018 19:28:41 GMT

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;

Re: proc sql link table

Reeza — Sun, 16 Sep 2018 19:35:58 GMT

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;

Re: proc sql link table

VDD — Sun, 16 Sep 2018 19:40:19 GMT

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;

Re: proc sql link table

Songchan — Sun, 16 Sep 2018 19:47:22 GMT

Thanks for replying,

but it still doesn't work

Re: proc sql link table

Songchan — Sun, 16 Sep 2018 19:48:41 GMT

No, still doesn't work

Re: proc sql link table

VDD — Sun, 16 Sep 2018 19:49:18 GMT

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

Re: proc sql link table

Songchan — Sun, 16 Sep 2018 19:55:30 GMT

No, still empty for data set temp04

Re: proc sql link table

PGStats — Sun, 16 Sep 2018 20:00:17 GMT

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

Re: proc sql link table

Songchan — Sun, 16 Sep 2018 20:11:20 GMT

Thank you PG! it works!