Getting data that occurred between two dates for a specific person

[mhoward2]

Okay lets say I have This data set

NAME	DATE IN	DATE OUT	VALUE
bob	20190519	20190601	5
jerry	20190306	20190324	11
tim	20190822	20190824	18

My goal is to get other rows of data that occurs between the DATE IN and DATE OUT for each person. So if I also have this other table that contains this data for bob

NAME	DATE	VALUE
bob	20190522	7
bob	20190528	14
bob	20190615	3

I want to be able to bring in the first two rows of data since they occured between the dates for bob from the first table, but not the third row since it occurred outside the DATE IN and DATE OUT. Please let me know if that doesn't make sense!

I thought about creating a macro variable that contained all dates between DATE IN and DATE OUT for bob and then using WHERE DATE IN (Macro Variable attached to bob's dates), but couldnt figure out how to get all dates between those two spots.

Lastly, my dates are SAS dates so I was using input(put(PREV_DSCHRG, yymmddn8.), 8.) to get them to numeric date values, please let me know if there's a better way to do this!

Thanks in advance!

[PeterClemmensen]

Here is one way. What should happen if the name is not present in the first data set?

data one;
input name $ (datein dateout)(:anydtdte.) value;
format datein dateout yymmddn8.;
datalines;
bob 20190519 20190601 5
jerry 20190306 20190324 11
tim 20190822 20190824 18
;

data two;
input name $ date :anydtdte. value;
format date yymmddn8.;
datalines;
bob 20190522 5
bob 20190528 11
bob 20190615 18
;

data want(drop=datein dateout rc);
   if _N_=1 then do;
      declare hash h(dataset:'one');
      h.definekey('name');
      h.definedata('datein', 'dateout');
      h.definedone();
   end;

   set two;
   datein=.; dateout=.;

   rc=h.find();

   if datein <= date <= dateout;
run;

[r_behata]

Data have1;
input NAME	$ DATE_IN:anydtdte. DATE_OUT :anydtdte.	VALUE;
format DATE_IN DATE_OUT date9.;
cards;
bob 20190519 20190601 5
jerry 20190306 20190324 11
tim 20190822 20190824 18
;
run;

data have2;
input NAME $ DATE :anydtdte. VALUE;
format DATE date9.;
cards;
bob 20190522 5
bob 20190528 11
bob 20190615 18
;
run;


data want;
	merge have1 have2;
	by name;

	if DATE_IN <= DATE <= DATE_OUT;
run;

[novinosrin · Accepted solution]

data have;
input NAME	$ (DATE_IN	DATE_OUT) (:yymmdd10.)	VALUE;
format DATE_IN	DATE_OUT yymmdd10.;
cards;
bob	20190519	20190601	5
jerry	20190306	20190324	11
tim	20190822	20190824	18
;

data have2;
input NAME $	DATE :yymmdd10.	VALUE;
format date yymmdd10.;
cards;
bob	20190522	7
bob	20190528	14
bob	20190615	3
;

proc sql;
create table want as
select b.*
from have a, have2 b
where a.name=b.name and date_in<=date<=date_out;
quit;

[mhoward2]

I know this was awhile ago but this worked great for me, thanks!

[Kurt_Bremser]

You already had SAS dates and converted them to this useless ****?

That's like giving away your Ferrari for a Trabant and being happy about it.

You only need to do this:

proc sql;
create table want as
select
  t1.name,
  t1.date_in,
  t1.date_out,
  t1.value as value_1,
  t2.date,
  t2.value as value2
from have1 t1, have2 t2
where t1.name = t2.name and t2.date between t1.date_in and t2.date_out
;
quit;

and keep the SAS dates, for $DEITY's sake!

[ballardw]

---

@mhoward2 wrote:

Lastly, my dates are SAS dates so I was using input(put(PREV_DSCHRG, yymmddn8.), 8.) to get them to numeric date values, please let me know if there's a better way to do this!

Thanks in advance!

---

That does NOT  create a SAS date value, it creates a numeric value that mimics a SAS date value. For put(PREV_DSCHRG, yymmddn8.) to have created expected values then your  PREV_DSCHRG variable started out as a SAS date variable.

If your Datein or Dateout variables were created this way you may want to test:

data want;

   set have;

   year = year(datein);

run;

and see if the Year values are the expected year for Datein.

Many comparisons may work but if you want to do anything with actual intervals or graphing of these dates you may not like the results.