Help using Base SAS procedures

Retaining fields for unusual scenario

Accepted Solution Solved
Reply
Super Contributor
Posts: 259
Accepted Solution

Retaining fields for unusual scenario


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

Her is my sample dataset:

Veh NoPremstartend
15001/1/101/3/10
102/3/101/6/10
102/6/1030/9/10
101/10/1031/12/10
26001/1/091/3/09
27001/1/091/3/09
202/3/0920/9/09
2021/9/0930/9/09

I want to create three extra fields as follows:

prem1start1end1
5001/1/1031/12/10
5001/1/1031/12/10
5001/1/1031/12/10
5001/1/1031/12/10
6001/1/091/3/09
7001/1/0930/09/09
7001/1/0930/09/09
7001/1/0930/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


Accepted Solutions
Solution
‎07-22-2015 08:19 AM
Super User
Posts: 10,023

Re: Retaining fields for unusual scenario

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

View solution in original post


All Replies
Solution
‎07-22-2015 08:19 AM
Super User
Posts: 10,023

Re: Retaining fields for unusual scenario

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 1 reply
  • 309 views
  • 0 likes
  • 2 in conversation