Help with data processing: how to create new marker variables from a series of dates

[cm_mp]

Hello!

I'm writing with the hope of getting some help with how to approach a data processing question.

I have a dataset that contains, for a sample of pregnant women, a series of start and end dates for insurance enrollment, as well as the date of birth. I need to create variables that are markers for whether or not each woman was insured during certain intervals of her pregnancy and after delivery (the last 60 days of her pregnancy, and the last 180 days after delivery). The dataset basically looks like the line below, where start1 is the start date of the first insurance enrollment period and end1 is the corresponding end date, etc. Women have up to 55 enrollment periods captured in the dataset.

ID DOB start1 start2 start3 ... start55 end1 end2 end3 ... end55

I have been trying to use arrays to look at all enrollment periods and identify the relevant enrollment periods with respect to the delivery date, however I'm having difficulty with the following scenario: When a woman's insurance expires and she re-enrolls (and therefore actually maintains her insurance), there are multiple enrollment periods contributing to a single continuous interval of insurance coverage. Code that counts women as being enrolled in insurance only if a single enrollment period covers the whole interval of interest misses women who were in fact enrolled for the interval of interest but happen to span multiple enrollment periods. (For example, for the period of the last 60 days of pregnancy, a woman may re-enroll 35 days prior to delivering and actually be continually enrolled in insurance, but an array looking for any interval with start <=60 days before DOB and end > DOB as a marker of continuous insurance would not count this woman as being insured.)

One approach I was thinking of trying is to write some sort of array or macro code that could run through every date in the observation window and give each individual date a value for insurance enrollment (0/1), and then I could do some sort of counting/summing of the 0/1s for the intervals of interest to see the number of days insured. To be honest I'm not really sure how I would achieve this, and I'd love to learn a more streamlined way to approach this question.

So, please help if you can!

Do you have any ideas about how I could manipulate my dataset:

ID DOB start1 start2 start3 ... start55 end1 end2 end3 ... end55

to generate variables that indicate:

1) if a woman was enrolled for the last 60 days of her pregnancy (last 60 days before DOB)

2) if a woman lost insurance for 30 or more days in the first 180 days post-partum (first 180 days after DOB)

taking into consideration that multiple enrollment periods could contribute?

Many thanks for any help you can offer!

[Larrihoover]

For these kind of situations in SAS, instead of trying to make a matrix with arrays, I usually go with some form of a function. To make your variable names you can just use &i or &load_dt. You will have to tinker with this because I do not have the data but I would go about it this way, calling a function like this (Looks more complicated than it is):

%let dob = JAN2010;
%put &dob.;

%macro date_loop(StartMonYY=, Start=1, Stop=, Factor=1, Direction=+);
%do i=&start %to &stop %by 1;
%let SnapDate=%sysfunc(intnx(month,"01&StartMonYY"d,&Direction(&i-1)*&factor,end),date9.);
%put Date for processing=&SnapDate;
/*Different Formats*/
%let MonYY = %sysfunc(intnx(month,"&SnapDate"d,0,E),monyy7.);
%put Month and Year = &MonYY;
%let load_dt = %sysfunc(intnx(month,"&SnapDate"d,0,E),yymmn6.);
%put Year and Month = &load_dt;

/*For Testing*/
%let approx_days = %eval(&i.*30);
%put Dob Pregnancy &approx_days;

%let approx_lost = %eval(180 - (&i.*30));
%if %eval(&approx_lost.< 0) %then %do;
%put Approx Lost GT 180 days;
%end;
%else %do;
%put 180 days minus Approx Lost = &approx_lost.;
%end;

%end;
%mend;

%date_loop(StartMonYY=&dob.,Start=1,Stop=55,Factor=1,Direction=+);

[cm_mp]

Thanks for your suggestion!

[RW9 · Accepted solution]

Post test data in the form of a datastep, following this post if necessary:

https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...

The best way to handle your data is to normalise it, have the data going down the page rather than across.  Forget the "Excel" way of thinking, your code will be 10 times harder and fall over a lot.  

Normalise your data and this task becomes a simple where clause.  E.g:

data want;
  set have;
  where start_date-dob <= 60;
run;

[cm_mp]

Thanks - I ended up making the data "long" instead of "wide" and was able to create the variables I needed.