Retaining fields for unusual scenario

[brophymj]

I am trying to use sas code to create two extra columns on my dataset as follows:

Her is my sample dataset:

Veh No	Prem	start	end
1	500	1/1/10	1/3/10
1	0	2/3/10	1/6/10
1	0	2/6/10	30/9/10
1	0	1/10/10	31/12/10
2	600	1/1/09	1/3/09
2	700	1/1/09	1/3/09
2	0	2/3/09	20/9/09
2	0	21/9/09	30/9/09

I want to create three extra fields as follows:

prem1	start1	end1
500	1/1/10	31/12/10
500	1/1/10	31/12/10
500	1/1/10	31/12/10
500	1/1/10	31/12/10
600	1/1/09	1/3/09
700	1/1/09	30/09/09
700	1/1/09	30/09/09
700	1/1/09	30/09/09

So for each vehicle number where a non-zero premium is followed by a certain number of records with a zero premium I want to retain the premium and start date fields for all the 0 premium records and retrofit the last end date of each sequence of (premium followed by 0 premium for each vehicle)

What I'm trying to do is allocate the premium across all the exposure records in proportion to the length of exposure

[Ksharp · Accepted solution]

Try this one :

Code: Program.sas

data have;
infile cards truncover expandtabs;
input VehNo Prem (start end) (: ddmmyy10.);
format start end ddmmyy10.;
cards;
1 500 1/1/10 1/3/10
1 0 2/3/10 1/6/10
1 0 2/6/10 30/9/10
1 0 1/10/10 31/12/10
2 600 1/1/09 1/3/09
2 700 1/1/09 1/3/09
2 0 2/3/09 20/9/09
2 0 21/9/09 30/9/09
;
data have;
 set have;
 by VehNo ;
 if first.VehNo or Prem ne 0 then n+1;
run;
data temp;
 set have;
 by n;
 if last.n;
 keep n end;
run;
data want;
 merge have temp(rename=(end=_end));
 by n; 
 retain _Prem _start;
 if first.n then do;_Prem =Prem ;_start=start;end;
 format _start _end ddmmyy10.;
 drop Prem n start end;
run;

Message was edited by: xia keshan

Message was edited by: xia keshan