topic Re: Getting data that occurred between two dates for a specific person in SAS Programming

Getting data that occurred between two dates for a specific person

mhoward2 — Tue, 26 Nov 2019 16:41:22 GMT

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!

Re: Getting data that occurred between two dates for a specific person

PeterClemmensen — Tue, 26 Nov 2019 16:45:18 GMT

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;

Re: Getting data that occurred between two dates for a specific person

r_behata — Tue, 26 Nov 2019 16:45:50 GMT

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;

Re: Getting data that occurred between two dates for a specific person

novinosrin — Tue, 26 Nov 2019 16:48:47 GMT


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;

Re: Getting data that occurred between two dates for a specific person

Kurt_Bremser — Tue, 26 Nov 2019 16:49:58 GMT

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!

Re: Getting data that occurred between two dates for a specific person

ballardw — Tue, 26 Nov 2019 16:55:18 GMT

@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.

Re: Getting data that occurred between two dates for a specific person

mhoward2 — Fri, 06 Dec 2019 20:40:16 GMT

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