Merging to files

[cjacobson45]

Good Morning,

Please help, I know this should be simple, but this isn't working the way I need it to.

I have two datasets one looks like this:

File 1

MHN   DOS

111       5/6/16

111       6/10/16

111       8/5/16

Second dataset

MHN       Admit       Discharge

111         1/1/16       7/1/16

111         2/2/17       3/2/17

What I need is File 1 returned with a flag whether that DOS falls between the second dataset. I tried to do this, but the way I have it programmed it doesn’t work, it is returning multiple records (which I get why) and it flags the one, but then looks at the second date in the second dataset and flags it as the other way.

End result of what I would need is:

File 1

MHN   DOS                      Inpt

111       5/6/16                   Y

111       6/10/16                 Y

111       8/5/16                   N

[novinosrin]

data one;
input MHN   DOS :mmddyy8.;
format dos mmddyy8.;
cards;
111       5/6/16
111       6/10/16
111       8/5/16
;

data two;
input MHN       (Admit       Discharge) ( :mmddyy8.);
format Admit       Discharge mmddyy8.;
cards;
111         1/1/16       7/1/16
111         2/2/17       3/2/17
; 
proc sql;
create table want as
select  a.*,ifc(max(admit<=DOS<=Discharge)>0,'Y','N') as Flag
from one a left join two b
on a.mhn=b.mhn
group by a.mhn,dos
order by mhn;
quit;

Hi @cjacobson45  Yes very straight forward. However, may i ask why Y and N rather than 1 and 0. I always find flag var with numbers better than letters. Well, up to you.

Or just simpler

proc sql;
create table want as
select  a.*,ifc(admit<=DOS<=Discharge,'Y','N') as Flag
from one a left join two b
on a.mhn=b.mhn and (admit<=DOS<=Discharge)
order by mhn;
quit;

[cjacobson45]

@novinosrinAwesome thanks!!  This worked!

[Kurt_Bremser]

Please post your code, so we can alert you to the place where you went wrong.

[Andygray · Accepted solution]

Hello @cjacobson45  Looks like your question has been answered. If so, plz mark the solution as accepted and close the thread

[Andygray]

Hello @cjacobson45 You marked my comment as the solution. lol

Please mark the appropriate solution.