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.
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
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)
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.