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
Hello @cjacobson45 Looks like your question has been answered. If so, plz mark the solution as accepted and close the thread
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;
@novinosrinAwesome thanks!! This worked!
Please post your code, so we can alert you to the place where you went wrong.
Hello @cjacobson45 Looks like your question has been answered. If so, plz mark the solution as accepted and close the thread
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!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.