How to convert a series of dates to series of var.

[shasank]

Hi Community,

Thank you for taking time to help.

I have a one row per person dataset with a series of dates-

ID	Startdate1	Enddate1	Startdate2	Enddate2
1	1-Jan-92	31-Mar-92	1-Aug-99	30-Sep-12
2	1-Apr-93	31-Dec-99

I want to convert this to a format of Enrolled1 to Enrolled312 where Enrolled1 = 1Jan1991 and Enrolled312 = 31Dec2016 (Monthly iteration). 

For ID - 1 enrollment ranges from 1-Jan-92 till 31-March-92 and 1-Aug-99 to 30-Sept-12. So the corresponding Enrolled[i] for these months should be = 1 else 0.

Can you help with the problem.

Thank you for your time.

[Reeza]

Are enrolled1-enrolled312 the same for every record in that enrolled1 = January1991 for everyone?

[shasank]

Yes. those are standard

[Tom]

Do you just have 2 pairs of dates?  If so then I would just spell out the logic.

Let's make your test data first.

data have ;
  input id $ Startdate1 Enddate1 Startdate2 Enddate2;
  informat s: e: date.;
  format s: e: yymmdd10.;
cards;
1 1-Jan-92 31-Mar-92 1-Aug-99 30-Sep-12
2 1-Apr-93 31-Dec-99 . .
;

Now let's create your flags.

data want ;
  set have;
  array enrolled [312];
  do i=1 to dim(enrolled); 
    s=intnx('month','01dec1990'd,i,'b');
    e=intnx('month',s,0,'e');
    enrolled[i]=(e>=startdate1 and s<=enddate1)
             or (e>=startdate2 and s<=enddate2)
    ;
  end;
  drop i s e;
run;

[shasank]

I have an array of multiple start and end dates.

[Tom]

---

@shasank wrote:
I have an array of multiple start and end dates.

---

How many? Why?

Personally if it is less than 15 or 20 I would just cut and paste more OR clauses. Sometimes wallpaper code is just easier to create and understand.

But you could program more arrays and another loop. Just make sure to set it to zero first and then set it to 1 when you get a hit.

data want2 ;
  set have;
  array enrolled [312];
  array sd startdate1-startdate2;
  array ed enddate1-enddate2;
  do i=1 to dim(enrolled); 
    s=intnx('month','01dec1990'd,i,'b');
    e=intnx('month',s,0,'e');
    enrolled[i]=0;
    do j=1 to dim(sd) while (enrolled[i]=0);
      enrolled[i]=(e>=sd[j] and s<=ed[j]);
    end;
  end;
  drop i j s e;
run;

[shasank]

Got it. I have about 21 I will try to cut and paste. Thank for the help. I will try to apply the algorithm

[heffo · Accepted solution]

Starting with the code from @Tom and adding some stuff. Should work dynamically. 

data have;
	input id $ Startdate1 Enddate1 Startdate2 Enddate2;
	informat s: e: date.;
	format s: e: yymmdd10.;
	cards;
1 1-Jan-92 31-Mar-92 1-Aug-99 30-Sep-12
2 1-Apr-93 31-Dec-99 . .
;
run;

data want;
	set have;
	array enrolled [312];
	array startDate [2]; *Set to the correct number, how many possible start and end dates are there?;
	array endDate [2]; *Set to the correct number, how many possible start and end dates are there?;

	*Init all the enroll variables to 0 first.;
	do _i=1 to dim(enrolled);
		enrolled[_i]=0;
	end;


	*Loop the startDate array. Assuming that all the start dates has an end date.;
	do _j=1 to dim(startDate);
		_startIndex=intck('month','01dec1990'd,startDate[_j]);
		_endIndex=intck('month','01dec1990'd,endDate[_j]);
		if _startIndex ne . then do; *Dont want to try to use start dates that are missing.;
			do _k=_startIndex to _endIndex; 
				enrolled[_k]=1;
			end;
		end;
	end;

	drop _:;
run;