Compare one variable in observation to different variable in all observations

sasuser123451 — Thu, 26 Dec 2019 16:20:31 GMT

Hi,

I am trying to determine therapy utilization rate based on the way that we have defined therapy use - ie within 180 days of diagnosis of the disease. I am trying to find a way to assign each ID (enrolid) its own denominator based on the number of people who were eligible for therapy at that time.

Variables involved here: enrolid, index_date (date of diagnosis of the disease), and svcdate (date that therapy was received). So for each enrolid, I would compare the date of therapy (svcdate) to the date of diagnosis (index_date) for all other enrolid's and want to get a sum of all enrolids whose date of diagnosis (index_date) was within 180 days prior to the date of therapy.

enrolid index_date svcdate denominator

123 2/1/2011 3/12/2011 ?

456 10/1/2012 11/4/2012 ?

789 1/1/2011 3/6/2011 ?

So here, the denominator for enrolid #123 would be 2, because enrolids 123 and 789 both have index_dates that are within 180 days (6 months) prior to the svcdate for enrolid #123. Same thing for enrolid #789. The denominator for enrolid #456 would be 1 because it is the only enrolid with an index_date within 180 days of its svcdate. There is only one observation per enrolid.

I know this syntax is not correct (I do not know how to reference the variable of the observation we are currently looking at vs the variable from a different observation we are comparing to), but this is the general idea of what I am thinking:

data want; set have; 
retain denominator 1 denominator_;
*setting denominator as one because each enrolid counts for its own denominator;

do while enrolid NE enrolid;               
**wrong syntax, but do while the enrolid of the observation we are comparing is not equal to the current enrolid ie compare to all other observations;

if intck('day',index_date,svcdate) > 0  and intck('day',index_date,svcdate) LE 180     
**wrong syntax, but compare this observation's svcdate to index_date of other observations;
*so I would want the svcdate to be the svcdate for the current observation and index_date to be the index_date for the observation we are comparing to;

then denominator+1; 
                                     
else denominator=denominator;

end;
run;

I am using SAS Enterprise Guide 8.1.

Thank you!

Re: Compare one variable in observation to different variable in all observations

Kurt_Bremser — Thu, 26 Dec 2019 16:31:27 GMT

Something like

proc sql;
create table want as
select
  a.*,
  (select count(*) from have b where b.enrolid ne a.enrolid and abs(b.indexdate - a.svcdate) le 180)
from have a;
quit;

Re: Compare one variable in observation to different variable in all observations

sasuser123451 — Thu, 26 Dec 2019 16:37:34 GMT

This code worked perfectly and accomplished exactly what I needed to accomplish. Thank you so much for your help, Kurt!

topic Re: Compare one variable in observation to different variable in all observations in SAS Programming

Compare one variable in observation to different variable in all observations

Re: Compare one variable in observation to different variable in all observations

Re: Compare one variable in observation to different variable in all observations