BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
cjacobson45
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
Andygray
Quartz | Level 8

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

View solution in original post

5 REPLIES 5
novinosrin
Tourmaline | Level 20

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
Calcite | Level 5

@novinosrinAwesome thanks!!  This worked!

Andygray
Quartz | Level 8

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

Andygray
Quartz | Level 8

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

 

Please mark the appropriate solution. 

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 5 replies
  • 2362 views
  • 4 likes
  • 4 in conversation