left join

manya92 — Mon, 06 Aug 2018 16:29:28 GMT

Hi all,

I am trying to make a dataset which has the following variable :

enrolid, index_date,period_start,period_stop, age,sex,svcdate and code

Now one exclusion criteria is that I have to remove patients having serious infection 30 days prior to the index_date.

I excluded them, but then i have to calculate incidence rate (IR). To calculate IR i need the variable svcdate but that variable is showing missing when i run this code. I want to know how can i get the svcdates var back by left joining.

This is my code

*ONLY SERIOUS INFECTIONS*/
proc sql ;	
	create table _02b_allserious as
	select distinct *
	from derived._02b_ser_inf_hosp 
	union
	select distinct *
	from derived._02b_ser_inf_iv 
	union 
	select distinct *
	from derived._02b_ser_inf_any ;
quit ;/*2489*/

/*JOIN WITH COHORT*/
proc sql;
	create table _02b_allserious2 as
	select distinct a.*,b.code,b.svcdate
	from _02b_hiv_tb3 as a 
	inner join _02b_allserious as b 
	on a.enrolid = b.enrolid 
	where (a.index_date-30) le b.svcdate le a.index_date;
quit ;/*2489*/


/*REMOVING PATIENTS WHO HAD A CLAIM BEFORE 30 DAYS FROM OUR COHORT  -STILL WILL HAVE PATIENTS HAVING SERIOUS INFECTIONS AFTER INDEX_DATE */
proc sql ;
	create table derived._02b_allserious3 as
	select distinct a.*
	from _02b_hiv_tb3 as a 
	left join _02b_allserious2 as b 
	on a.enrolid = b.enrolid 
	where b.enrolid is NULL ;
quit ;/*4042*/

Re: left join

UdayGuntupalli — Mon, 06 Aug 2018 18:48:35 GMT

@manya92,
Since I can't see the data, it is hard for me to troubleshoot. Is it possible your application of distinct during the join causing it ? Could you kindly remove the distinct during the join operation and try to filter for distinct values in a separate step ?

topic left join in SAS Programming

left join

Re: left join