topic Re: Merging to files in SAS Programming

Merging to files

cjacobson45 — Wed, 02 Jan 2019 14:51:17 GMT

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

Re: Merging to files

novinosrin — Wed, 02 Jan 2019 15:26:16 GMT


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;

Re: Merging to files

Kurt_Bremser — Wed, 02 Jan 2019 18:31:30 GMT

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

Re: Merging to files

cjacobson45 — Wed, 02 Jan 2019 21:37:40 GMT

@novinosrinAwesome thanks!! This worked!

Re: Merging to files

Andygray — Wed, 02 Jan 2019 22:11:39 GMT

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

Re: Merging to files

Andygray — Thu, 03 Jan 2019 18:47:40 GMT

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

Please mark the appropriate solution.