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
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.
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;
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;
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
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.
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.
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.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.