topic Using Array to compare and keep records based upon dates in SAS Programming

Using Array to compare and keep records based upon dates

BrianB4233 — Thu, 08 Jul 2021 21:29:31 GMT

Hello all,

I've been trying, and failing, to construct an array that compares dates across a row and sets a flag on that record to keep it or not. The 1st three date columns (trandt1-trandt3) are the transposed treatment dates for a patient (this patient had 3 on 26Dec17, 22Jan19, and 07Feb20), the next date column is the treatment date itself, and the last column is the date a score was assigned. Where keeper is equal to '1' are the rows I want to keep, and this b/c 1) the treatment date is equal to the score date or 2) the score is > treatment date but not > the other transposed treatment dates. Make sense?

data visits;
input id trandt1 :date9. trandt2 :date9. trandt3 :date9. visit_dt :date9. score_dt :date9. keeper;
format trandt1 :date9. trandt2 :date9. trandt3 :date9. visit_dt :date9. score_dt :date9.;
datalines;
1 26Dec2017 22Jan2019 07Feb2020 26Dec2017 26Dec2017 1
1 26Dec2017 22Jan2019 07Feb2020 26Dec2017 03Jan2018 1
1 26Dec2017 22Jan2019 07Feb2020 26Dec2017 22Jan2019 0
1 26Dec2017 22Jan2019 07Feb2020 26Dec2017 07Feb2020 0
1 26Dec2017 22Jan2019 07Feb2020 26Dec2017 13Feb2020 0
1 26Dec2017 22Jan2019 07Feb2020 22Jan2019 22Jan2019 1
1 26Dec2017 22Jan2019 07Feb2020 22Jan2019 07Feb2020 0
1 26Dec2017 22Jan2019 07Feb2020 22Jan2019 13Feb2020 0
1 26Dec2017 22Jan2019 07Feb2020 07Feb2020 07Feb2020 1
1 26Dec2017 22Jan2019 07Feb2020 07Feb2020 13Feb2020 1
;
run;

Re: Using Array to compare and keep records based upon dates

PaigeMiller — Thu, 08 Jul 2021 21:51:09 GMT

Often, it is best not to impose a condition on how the solution should be programmed. This does not strike me as case where an ARRAY would be helpful.

Of course, the wording used in your explanation is somewhat vague and even pencil and paper I cannot match your keeper values. In particular, I cannot make these words align with the values of keeper in your data set: "the score is > treatment date but not > the other transposed treatment dates".

Do you perhaps mean "the score is > treatment date but not > maximum of the other transposed treatment dates"

or do you perhaps mean "the score is > treatment date but not > minimum of the other transposed treatment dates"

or do you perhaps mean something else?

Here is sample code that can be modified depending on what you actually mean, and which does not use arrays.

data visits;
input id trandt1 :date9. trandt2 :date9. trandt3 :date9. visit_dt :date9. score_dt :date9.;
format trandt1 :date9. trandt2 :date9. trandt3 :date9. visit_dt :date9. score_dt :date9.;
datalines;
1 26Dec2017 22Jan2019 07Feb2020 26Dec2017 26Dec2017 1
1 26Dec2017 22Jan2019 07Feb2020 26Dec2017 03Jan2018 1
1 26Dec2017 22Jan2019 07Feb2020 26Dec2017 22Jan2019 0
1 26Dec2017 22Jan2019 07Feb2020 26Dec2017 07Feb2020 0
1 26Dec2017 22Jan2019 07Feb2020 26Dec2017 13Feb2020 0
1 26Dec2017 22Jan2019 07Feb2020 22Jan2019 22Jan2019 1
1 26Dec2017 22Jan2019 07Feb2020 22Jan2019 07Feb2020 0
1 26Dec2017 22Jan2019 07Feb2020 22Jan2019 13Feb2020 0
1 26Dec2017 22Jan2019 07Feb2020 07Feb2020 07Feb2020 1
1 26Dec2017 22Jan2019 07Feb2020 07Feb2020 13Feb2020 1
;
run;

data want;
    set visits;
    if (score_dt=visit_dt) or (score_dt>visit_dt and score_dt<=max(of trandt1-trandt3)) then keeper=1;
    else keeper=0;
run;

Re: Using Array to compare and keep records based upon dates

BrianB4233 — Thu, 08 Jul 2021 22:02:01 GMT

Thank you for your reply, and agreed, my wording is a bit vague. What I'm looking for is your 2nd condition: "the score is > treatment date butnot > minimum of the other transposed treatment dates".

Re: Using Array to compare and keep records based upon dates

PaigeMiller — Thu, 08 Jul 2021 22:06:54 GMT

@BrianB4233 wrote:
Thank you for your reply, and agreed, my wording is a bit vague. What I'm looking for is your 2nd condition: "the score is > treatment date butnot > minimum of the other transposed treatment dates".

Then this is a simple change to the code I provided.