Do-loop question

Reply
Occasional Contributor
Posts: 8

Do-loop question

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.

 

 

 

 

 

 

SAS Super FREQ
Posts: 9,326

Re: Do-loop question

Posted in reply to msbrvamsi
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
Trusted Advisor
Posts: 1,312

Re: Do-loop question

Posted in reply to msbrvamsi

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.
PROC Star
Posts: 1,599

Re: Do-loop question

[ Edited ]
Posted in reply to msbrvamsi

 

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;

PROC Star
Posts: 1,218

Re: Do-loop question

Posted in reply to msbrvamsi
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;
Super User
Posts: 10,695

Re: Do-loop question

Posted in reply to msbrvamsi
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;
Ask a Question
Discussion stats
  • 5 replies
  • 236 views
  • 0 likes
  • 6 in conversation