topic Re: Choosing data based on dates and times in SAS Data Management

Choosing data based on dates and times

Solvej — Thu, 01 Nov 2018 14:29:17 GMT

Dear SAS experts

I have the following programme where I am trying to merge my original data (data set x with information on multiple events for individual persons) with data on medicine (dataset medicin) given four hours before the event (datetime specified in dateset x (variable called startdatetime)).

proc sql;
create table meds4hoursbefore as
select x.*, medicin.*
from x
left join
medicin
on x.Patient_RK = medicin.Patient_RK
and medicin.medicinadmdatetime between (x.startdatetime - (4*60*60)) and x.startdatetime
left join
x next
on x.Patient_RK = next.Patient_RK
and x.xnr+1 = next.xnr
and next.startdatetime < medicin.medicinadmdatetime
where next.Patient_RK is null
order by Patient_RK, xnr
;
quit;

The patient id has to match in the two datasets and the programme also has to take into account the multiple events for the individual where we each time has to match with the appropriate medicine data.

My problem is that in dataset x I have 1412 individuals with 4140 events however after merging then I still have 1412 indiduals but only 3785 event. Why am I missing events? It can not be because no medicine data fits the event because some of the 3785 events that remain in the output also dont have at medicine match. I have 2,5 million records in my medicine data.

I hope this makes sense,

Kind regards

Solvej

Re: Choosing data based on dates and times

SuryaKiran — Thu, 01 Nov 2018 14:44:57 GMT

You have a where clause, which my guess is filtering the records. How are your startdatetime values, 4*60*60 might not be the right value.

Please provide some sample data and your expected output.

Re: Choosing data based on dates and times

Solvej — Thu, 01 Nov 2018 14:59:19 GMT

Thank you. When I remove this I dont loose the patients however at lot more data is matched with my events. I wonder what the statement did. Do you know?
Kind regards

Re: Choosing data based on dates and times

SuryaKiran — Thu, 01 Nov 2018 17:45:37 GMT

That is not a left join works when you have multiple values on the join condition.

Check this example and see the final output.

data one;
input id value;
datalines;
1 123
2 345
;
run;
data two;
input id sales;
datalines;
1 10
1 20
1 30
2 10
2 30
2 40
;
run;

proc sql;
select a.id,a.value,b.sales
from one as a
left join two b
	on a.id=b.id;
quit;

If you want only one record from the second table to be joined to first table, then you need to tell which record to be joined. There are several ways to remove duplicate records.

Here is one example:

proc sql;
select a.id,a.value,b.sales
from one as a
left join two b
	on a.id=b.id
group by a.id
having sales=max(sales);
quit;