topic Re: PROC SQL: Inputting dates into time periods and creating missing values when needed in SAS Data Management

PROC SQL: Inputting dates into time periods and creating missing values when needed

Kels123 — Tue, 29 Nov 2016 23:51:57 GMT

I have a database of time periods that represent different environments (the same study subject can have multiple time periods depending on how many environments they were in). I have a second dataset containing clinic visits and the corresponding dates of those visits. I would like to input these clinic visits (and dates) into the appropriate time periods (delineated by startdate and enddate). To do this, I have a code like this, which sort of works:

/*combine time periods and visit datasets together, keep records where visit date between start and end dates for each period*/

proc sql;

create table allvisits as

select i.*, v.*

from kelsey.periods i full outer join kelsey.clinicvisits v

on i.subjectid=v.subjectid

where i.startdate<=v.visitdate<=i.enddate ;

quit;

The problem is that there are sometimes time periods where no clinic visits took place during those time periods; the code below results in those time periods getting dropped and I don't want to do that because I am also interested in situations were no clinic visit was done. Is there a way to modify this code so that for records where there is no visit date that corresponds to a time period's start and end dates, I can still retain that time period and make the visitdate=missing? Thank you in advance.

Re: PROC SQL: Inputting dates into time periods and creating missing values when needed

Reeza — Wed, 30 Nov 2016 00:06:03 GMT

You could but your probably better off handling that in your end reporting. Depending on the proc used to summarize results you may be able to add the time period in at that point more easily.

Re: PROC SQL: Inputting dates into time periods and creating missing values when needed

PGStats — Wed, 30 Nov 2016 04:02:43 GMT

It is not clear why you use a full outer join. I would do:

proc sql;
create table allvisits as
select 
	i.*, 
	v.subjectId as visitSubjectId,
	v.visitdate /* More visits fields */
from 
	kelsey.periods as i left join
	kelsey.clinicvisits as v on 
		i.subjectid = v.subjectid and 
		v.visitdate between i.startdate and i.enddate;
quit;