Calcite | Level 5

## Compare one variable in observation to different variable in all observations

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

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

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;``````
2 REPLIES 2
Super User

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

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;``````
Calcite | Level 5

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

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

Discussion stats
• 2 replies
• 481 views
• 1 like
• 2 in conversation