Help with expression or subquery in an SQL JOIN

[EinarRoed]

Hello,

Imagine that you're working in DI Studio. You have an SQL Join with the two following input tables:

Input #1: DEAL_MAIN

DEAL_NO		POL_NO		LAPSE_DT
1		172		AUG13
1		173		SEP13
2		185		JUL13
2		186		MAY13
2		187		SEP13
3		222		FEB13
3		223		OCT13
4		301		MAY13
4		302		JUN13
4		303		APR13

Input #2: DEAL_POL

DEAL_NO		POL_NO
1		173
2		186
3		222
4		301

They are joined by a LEFT JOIN (DEAL_NO=DEAL_NO), because *all* the rows in the table DEAL_MAIN must be included.

My problem is this: The column LAPSE_DT in the main table must display only the date where there's a match on POL_NO in the second table. So, the output work table should look like this (only LAPSE_DT has changed):

DEAL_NO		POL_NO		LAPSE_DT
1		172		SEP13
1		173		SEP13
2		185		MAY13
2		186		MAY13
2		187		MAY13
3		222		FEB13
3		223		FEB13
4		301		MAY13
4		302		MAY13
4		303		MAY13

Can someone please advice me on how to accomplish this? I suspect that this can be solved by means of an expression for the LAPSE_DT variable, or a subquery, but I can't seem to get it right.

In any case, thanks for your time.

[sam369]

Hi EinarRoed,

Some one might come with slick answer !!! here is one apporach

data have;

input DEAL_NO  POL_NO  LAPSE_DT $;

cards;

1  172  AUG13

1  173  SEP13

2  185  JUL13

2  186  MAY13

2  187  SEP13

3  222  FEB13

3  223  OCT13

4  301  MAY13

4  302  JUN13

4  303  APR13

;

run;

data have2;

input DEAL_NO  POL_NO;

cards;

1  173

2  186

3  222

4  301

;

run;

proc sql;

create table temp as

select a.*,b.flag

from have as a left join(select *,1 as flag from have2) as b

on a.DEAL_NO=b.DEAL_NO & a.POL_NO=b.POL_NO

having calculated flag eq 1;

quit;

proc sql;

create table want as

select a.*,b.LAPSE_DT

from have(drop=LAPSE_DT) as a left join temp as b

on a.DEAL_NO=b.DEAL_NO;

quit;

Thanks

Sam

[LinusH]

You don't need a specific flag column, just test one of the join columns if it is not null.

In the expression builder (or Case builder):

case when deal_pol.pol_no = . then .

else lapse_dt

end

(as lapse_dt)