SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Help with expression or subquery in an SQL JOIN

Reply
Frequent Contributor
Posts: 90

Help with expression or subquery in an SQL JOIN

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_NOPOL_NOLAPSE_DT
1172AUG13
1173SEP13
2185JUL13
2186MAY13
2187SEP13
3222FEB13
3223OCT13
4301MAY13
4302JUN13
4303APR13

Input #2: DEAL_POL

DEAL_NOPOL_NO
1173
2186
3222
4301

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_NOPOL_NOLAPSE_DT
1172SEP13
1173SEP13
2185MAY13
2186MAY13
2187MAY13
3222FEB13
3223FEB13
4301MAY13
4302MAY13
4303MAY13

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.

Regular Contributor
Posts: 168

Re: Help with expression or subquery in an SQL JOIN

Posted in reply to EinarRoed


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

Super User
Posts: 5,441

Re: Help with expression or subquery in an SQL JOIN

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)

Data never sleeps
Ask a Question
Discussion stats
  • 2 replies
  • 291 views
  • 6 likes
  • 3 in conversation