left join

Reply
Contributor
Posts: 73

left join

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*/
Contributor
Posts: 43

Re: left join

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

Ask a Question
Discussion stats
  • 1 reply
  • 64 views
  • 0 likes
  • 2 in conversation