Do-loop question

[msbrvamsi]

My data looks like this

Policy Number	Policy Start date	Policy End date	Policy Term	Age
101	10-Aug-15	8-Aug-18	3	58
102	25-Feb-16	23-Feb-18	2	55

i need the output in the below format. i wanted to break policy start date and Policy end date into years by adding 364 days

Policy Start date	Policy End date	Policy End date	Start Date	End date	Policy Term	Age	Policy Year
101	10-Aug-15	8-Aug-18	10-Aug-15	8-Aug-16	3	58	1
101	10-Aug-15	8-Aug-18	9-Aug-16	8-Aug-17	3	59	2
101	10-Aug-15	8-Aug-18	9-Aug-17	8-Aug-18	3	60	3
102	25-Feb-16	23-Feb-18	25-Feb-16	23-Feb-17	2	55	1
102	25-Feb-16	23-Feb-18	24-Feb-17	23-Feb-18	2	56	2

Please let me know if you any query. Can someone help me on this for me.

[Cynthia_sas]

Hi, this is a good scenario for using a DO loop with and OUTPUT statement inside the loop. It will do exactly what you want. You can advance the Policy Start date by either adding 364 or you can use the INTNX function.

Either way, you should find good examples in the documentation and in previous forum postings.

cynthia

[mkeintz]

You could:

1. Set the initial START_DT=policy_start_date.
2. Set END_DT=policy_end_date.  Then, in a loop, use INTNX to repeatedly step backwards by 1 year, as long as you have END_DT more than 12 months after POLICY_START_DATE until you have END_DT.  When END_DT is no longer > 12 month later, you have the initial END_DT
3. In a subsequent loop, output the record, make the new start_dt= 1 day after end_dt, then increment end_dt by 1 year.  The loop should stop when start_dt exceeds policy_end_date.

[novinosrin]

data have;

input PolicyNumber      PolicyStartdate :date7. PolicyEnddate :date7.   PolicyTerm  Age   ;

format      PolicyStartdate  PolicyEnddate  date7.;

datalines;

101   10-Aug-15   8-Aug-18    3     58

102   25-Feb-16   23-Feb-18   2     55

;

data want;

set have;

do policyyear=1 to policyterm;

if    policyyear=1 then startdate=PolicyStartdate;

else if Policyyear>1 then do;

startdate=enddate+1;

age=age+1;

end;

enddate =intnx('year',PolicyEnddate , -1*(policyterm-policyyear), 's');

output;

end;

format startdate enddate date7.;

run;

[PeterClemmensen]

data have;
input Policy_Number$ (Policy_Start_date Policy_End_date)(:date9.) Policy_Term Age;
format Policy_Start_date Policy_End_date date9.;
datalines;
101 10-Aug-15 8-Aug-18 3 58
102 25-Feb-16 23-Feb-18 2 55
;

data want(drop=i);
	format Policy_Number Policy_Start_date Policy_End_date StartDate EndDate Policy_Term Age PolicyYear;
	set have;
	do i=1 to Policy_Term;
		if i=1 then StartDate=Policy_Start_date;
		else StartDate=EndDate+1;
		EndDate=intnx('day', StartDate, 364, 'sameday');
		age+1;
		PolicyYear=i;
		output;
	end;
	format StartDate EndDate date9.;
run;

[Ksharp]

data have;
input Policy_Number$ (Policy_Start_date Policy_End_date)(:date9.) Policy_Term Age;
format Policy_Start_date Policy_End_date date9.;
datalines;
101 10-Aug-15 8-Aug-18 3 58
102 25-Feb-16 23-Feb-18 2 55
;
data want;
 set have;
 start=Policy_Start_date;
 _age=age;
 do PolicyYear=1 to Policy_Term;
  age=_age+PolicyYear-1;
  end=intnx('month',Policy_End_date,-PolicyYear+1,'s');
  output;
  start=end+1;
 end;
 format start end date9.;
 drop _age ;
 run;