topic Re: Do-loop question in SAS Programming

Do-loop question

msbrvamsi — Fri, 22 Dec 2017 03:50:40 GMT

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.

Re: Do-loop question

Cynthia_sas — Fri, 22 Dec 2017 04:32:25 GMT

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

Re: Do-loop question

mkeintz — Fri, 22 Dec 2017 04:41:35 GMT

You could:

Set the initial START_DT=policy_start_date.
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
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.

Re: Do-loop question

novinosrin — Fri, 22 Dec 2017 16:39:01 GMT

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;

Re: Do-loop question

PeterClemmensen — Fri, 22 Dec 2017 07:47:50 GMT

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;

Re: Do-loop question

Ksharp — Fri, 22 Dec 2017 14:29:15 GMT

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;