Use Left Join where clause

fafrin420 — Sun, 10 Jun 2018 03:38:23 GMT

proc sql;
  create table master_table as select a.*, b.*
  from dataseta a left join datasetb b
  on a.permno = b.permno
  where intnx('month',a.date,12,'E')>= b.date
  group by a.permno, a.date;
  quit;

Hi,

My dataset b contains r_size, size_min, size_max, permno date. This dataset has rows of observation for the month of June. The dataset a dates that ranges for all month. I want to merge the dataset b with dataset a matching permno of both dataset and giving the same row from dataset b to every month of year in dataset a that follows the month of June in dataset b .

For example, as an end output, for permno 92719, r_size should be 0, size_min should be 0 etc. for 31DEC1990 in the merged dataset.

:Dataset ab

Re: Use Left Join where clause

PGStats — Sun, 10 Jun 2018 05:45:12 GMT

I guess you may need to do:

proc sql;
create table master_table as 
select 
    a.*, 
    b.r_size, 
    b.size_min, 
    b.size_max,
    b.date as date_b
from 
    dataseta a left join 
    datasetb b 
        on  a.permno=b.permno and 
            year(a.date) = year(b.date);
quit;

topic Use Left Join where clause in SAS Studio

Use Left Join where clause

Re: Use Left Join where clause