Check if visit value in one dataset is between the visit values in another dataset

[sas_alias]

Hi,

I need some help here. I am trying to compare values of a visit variable in one dataset(Assessment )with that of a variable in another dataset(Treatment) to populate the treatment the subject was having during that visit.

Assessment:

ID   VISIT  VISDT  

A      1         10

A      2         11

A      3         12

B      1         11

B      2         12

B      3         13

Treatment:

ID  TRT  TRTDT

A     x         9

A     x         10

A     y         11

A     x         12

B     x         11

B     y         12

B     y         14

B     x         15

I am trying to compare the VISDT from Assessment dataset with that of the Treatment dataset. If the VISIDT is equal to or between the TRTDT then I want the respective TRT values populated for Assessment dataset.

Thanks in advance.

[Reeza]

What do you mean by "or between the TRTDT"?

Can you show the output you'd expect for the data you've shown?

---

@sas_alias wrote:

Hi,

I need some help here. I am trying to compare values of a visit variable in one dataset(Assessment )with that of a variable in another dataset(Treatment) to populate the treatment the subject was having during that visit.

Assessment:

ID   VISIT  VISDT  

A      1         10

A      2         11

A      3         12

B      1         11

B      2         12

B      3         13

Treatment:

ID  TRT  TRTDT

A     x         9

A     x         10

A     y         11

A     x         12

B     x         11

B     y         12

B     y         14

B     x         15

I am trying to compare the VISDT from Assessment dataset with that of the Treatment dataset. If the VISIDT is equal to or between the TRTDT then I want the respective TRT values populated for Assessment dataset.

Thanks in advance.

---

[sas_alias]

Hi Reeza,

the final output should be as below

ID   VISIT  VISDT  TRTDT                                       TRT

A      1         10           10                                             x

A      2         11           11                                              y

A      3         12           12                                             x

B      1         11           11                                             x

B      2         12           12                                             y

B      3         13           (between 12 and 14)                y

Thanks.

[Astounding]

See if this gives you what you want.  Sort both data sets by ID and date.  Then:

data want;

set treatment (rename=(trtdt=visitdt) in=new_treatment) assessment (in=keepme);

by id visitdt;

retain treatment;

if new_treatment then treatment = trt;

else if first.id then treatment=" ";

if keepme;

drop trt;

run;

Note that the treatment variable will now be named Treatment, not TRT.