topic Re: How to create a flag for each month lying between 2 dates. (Interpolation?) in SAS Programming

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

vpgodbole — Fri, 02 Feb 2018 00:22:39 GMT

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

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

MINX — Fri, 02 Feb 2018 03:57:04 GMT

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;

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

Astounding — Fri, 02 Feb 2018 04:06:16 GMT

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;

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

vpgodbole — Fri, 02 Feb 2018 05:01:26 GMT

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

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

Astounding — Fri, 02 Feb 2018 07:57:44 GMT

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.

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

vpgodbole — Thu, 08 Feb 2018 02:10:03 GMT

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.

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

Astounding — Thu, 08 Feb 2018 02:28:20 GMT

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.