topic Re: Help with expression or subquery in an SQL JOIN in SAS Data Management

Help with expression or subquery in an SQL JOIN

EinarRoed — Tue, 16 Apr 2013 13:50:57 GMT

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.

Re: Help with expression or subquery in an SQL JOIN

sam369 — Tue, 16 Apr 2013 14:19:40 GMT

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

Re: Help with expression or subquery in an SQL JOIN

LinusH — Tue, 16 Apr 2013 14:49:59 GMT

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)