How to create a flag for each month lying between 2 dates. (Interpolation?)

[vpgodbole]

Dear All,

I have claim number with Claim start date of 01 Jan 2017 and Claim closed date of 04th April 2017. So, the months during which this claim was open are Jan, Feb and March.

My requirement is to create one variable for each month and year; i.e 12 variable in each year. This variable should be Flag=1 if the claim is open during that month. The data i have for analysis comprises years 2016,2017 so at the most I am expecting 24 flags. Difference between dates should be reflected in the flags as =1 marked for each month its open. The closed month should not be flagged.

About the file:

Its a flat file.

example:

Clm_number  Clm_Start_Dt   Clm_Closed_dt

101                 01JAN2017      04APR2017

102                 29MAR2016    16AUG2016

103                 28NOV2016    08SEP2017

I am stuck here thinking how i can automate this and accommodate any year / any date. I am using SAS EG 5.1.

Thank you All and any feedback is appreciated!

-

Varun

[MINX]

Hope it is what you want

data have;
infile datalines;
input Clm_number @5 Clm_Start_Dt date9. @15 Clm_Closed_dt date9.;
format Clm_Start_Dt Clm_Closed_dt date9.;
datalines;
101 01JAN2017 04APR2017
102 29MAR2016 16AUG2016
;
run;

data want;
	set have;
	m_start=month(Clm_Start_Dt);
	m_end=month(Clm_Closed_dt)-1;
	array month(12) JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC;
	do i=m_start to m_end;
	    month(i)=1;
	end;
	drop m_start m_end i;
run;

[Astounding]

Worth trying:

data have;

input claim Clm_start_dt : date9. Clm_closed_dt : date9.;

format Clm_start_dt Clm_closed_dt date9.;

cards;

101                 01JAN2017      04APR2017

102                 29MAR2016    16AUG2016

103                 28NOV2016    08SEP2017

;

data want;

set have;

array flags {24} flag_201501 - flag_201512 flag_201601 - flag_201612;

start = 12 * (year(Clm_start_dt) - 2015) + month(Clm_start_dt);

finish = 12 * (year(Clm_closed_dt) - 2015) + month(Clm_closed_dt);

do k=start to finish - 1;

   flags{k} = 1;

end;

drop k start finish;

run;

[vpgodbole]

Thanks both... below code did the job, I just made first 12 dummy variables since i was getting this error 'ERROR: Array subscript out of range at line 28 column 4' :on submitting code mentioned by Astounding. also i changed the variable names of years to 16 and 17.

data want;

set have;

array flags {*} dum1 - dum12 flag_201601 - flag_201612 flag_201701 - flag_201712;

start = 12 *(year(Clm_start_dt) - 2015) + month(Clm_start_dt) ;

finish = 12 * (year(Clm_closed_dt) - 2015) + month(Clm_closed_dt) ;

do k=start to (finish - 1) ;

flags{k} = 1;

end;

/*drop k start finish;*/

run;

I realized why 12 was multiplied, good idea, but then realized it was jumping to 13th var directly. so created dummies.

Now, to make it bit complicated.... what if a claim is closed but reopened again and closed again.

Ex. 

Clm_number  Clm_Start_Dt     Clm_Closed_Dt     Clm_Reopened_Dt     Clm_Closed_dt2

103                 28NOV2016       08FEB2017          16JUN2017                  03AUG2017

Thank you again,

Varun

[Astounding · Accepted solution]

For a reopened claim, add another loop at the end of the DATA step:

start = 12 *(year(Clm_reopened_dt) - 2015) + month(Clm_reopened_dt) ;

finish = 12 * (year(Clm_closed_dt2) - 2015) + month(Clm_closed_dt2) ;

do k=start to (finish - 1) ;

flags{k} = 1;

end;

Also note, you could eliminate the error message by a slight change to my original code, without adding 12 dummy variables.  Just change the formula to subtract 2016 instead of 2015.  That assumes that all dates fall into either 2016 or 2017.

[vpgodbole]

Thank you however, dummy variables are needed because even if 2016 , 2017 were to be replaced , the array goes to 13 th variable directly.  please correct if I am wrong here.

This code works thank you.

[Astounding]

The array can begin at the 1st element of the array.  It depends on what years are in the data set.  

The formula that subtracts 2015 from the year assumes that the year will be at least 2015.  For 2015, the array begins with the 1st element, and for 2016 the array begins with the 13th element.

If you have more than 2 years of data in the data, you need more elements in the array.

If the data contains just 2016 and 2017 data, the formula needs to change.  Subtract 2016 instead of 2015, and there won't be any dummy elements needed.