topic Re: Creating a new variable/flag based on flag indicator with latest date within a range in SAS Programming

Creating a new variable/flag based on flag indicator with latest date within a range

epialy — Thu, 08 Dec 2022 19:04:24 GMT

Hi all, I've been spinning my wheels on this for a while and hoping to get some help. I have a series of flag indicators with associated dates, and need to select the last date within a range (a year from a baseline date), and then create a new variable based on a flag associated with that last date.

The data look kind of like this:

studyID	baselinedate	Flag1	Date1	Flag2	Date2	Flag3	Date3
1	01/10/2000	1	02/04/2000	0	05/10/2000	1	06/17/2000
2	04/29/2008	1	07/07/2008	0	08/08/2008
3	09/13/2004	1	09/29/2004	0	05/05/2008

So, for all of them, I want to scan Date1, Date2, and Date3 for dates that are within a year of the baselinedate. For the first studyID, because all three dates are within a year, and the latest flag (Flag3)=1, I then want NewVariable=1. For studyID=2, both dates are within a year of baseline but because latest flag Flag2=0, I want NewVariable=2. For studyID=3, only Date1 is with the year and because that flag is 1, then NewVariable=1 (and Flag2/Date2 are ignored as they are past the year mark).

I've been trying to do this within a data step but I am feeling like the conditionals involved here might be too complex? But not sure how else to approach. Any help would be much appreciated. Thank you!

Re: Creating a new variable/flag based on flag indicator with latest date within a range

PaigeMiller — Thu, 08 Dec 2022 20:10:00 GMT

/* UNTESTED CODE */

data want;
    set have;
    array d date1-date3;
    array f flag1-flag3;
    do i = 1 to dim(d);
        if d(i)-baselinedate > 365 then d(i)=.;
    end;
    max_date_within_1yr = max(of d(*));
    flag_max_date = f(max_date_within_1yr,whichn(of d(*)));
    drop i;
run;

If you set dates to beyond 1 year equal to missing, the calculations are much simpler. After the code sets these dates beyond 1 year to missing, we find the max value, and then we find the flag associated with that value. That gives you the 0 or 1 for that max date.

If you want tested code, please provide data as working SAS data step code, which you can type in yourself, or which you can get from these instructions. We cannot work from data that is a screen capture or file attachment.

Re: Creating a new variable/flag based on flag indicator with latest date within a range

s_lassen — Fri, 09 Dec 2022 14:58:48 GMT

Please post your example data as a data step, e.g.:

data have;
  infile cards missover;
  input studyID baselinedate mmddyy10. +1 Flag1	Date1 mmddyy10. +1 Flag2 Date2 mmddyy10. +1 Flag3 Date3 mmddyy10.;
  format baselinedate Date1 Date2 Date3 mmddyy10.;
cards;
1 01/10/2000 1 02/04/2000 0 05/10/2000 1 06/17/2000
2 04/29/2008 1 07/07/2008 0 08/08/2008	 	 
3 09/13/2004 1 09/29/2004 0 05/05/2008	 	 
;run;

Assuming that the dates are sorted (as they are in your example) this should work:

data want;
  set have;
  array flags(*) flag1-flag3;
  array dates(*) date1-date3;
  new_variable=0;
  do _N_=1 to dim(flags);
    if missing(dates(_N_)) then leave;
    if intck('YEAR',baselinedate,dates(_N_))<1 then
      new_variable=flags(_N_);
    else leave;
    end;
run;

I am not quite sure what you want to do with dates exactly one year after the baseline, in the above code they are not considered.

If you want to include them, change the IF statement to

    if intck('YEAR',baselinedate,dates(_N_),'C')<=1 then

Re: Creating a new variable/flag based on flag indicator with latest date within a range

s_lassen — Fri, 09 Dec 2022 14:57:02 GMT

Your code

if d(i)-baselinedate > 365 then d(i)=.;

is a bit problematic, regarding leap years (and actually all the years in the example data are leap years, with 366 days).

I think it is better to use the INTCK function.

Re: Creating a new variable/flag based on flag indicator with latest date within a range

epialy — Mon, 12 Dec 2022 23:41:46 GMT

Thank you! I was able to tweak this for exactly what I needed.