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*/
@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 ?
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.