## Retaining

Solved
Super Contributor
Posts: 261

# Retaining

Hi

I'm trying to adjust my dataset as follows:

The "before" dataset below is a sample from my existing dataset; the "after" is where I want it to be. Basically, the vehicle_years field is populated in the first entry of each year and then if there is a change over the year the change is given; all other entries are 0. I want (for each policy and each year) to cumulate the initial figure over each following transaction in the same year.

data before;
infile datalines dlm=',';
input policy_no  year  vehicle_nos 5. ;
datalines ;
123,2010,1233
123,2010,1
123,2010,0
123,2011,1234
123,2011,0
123,2011,1
123,2011,0
123,2011,4
123,2012,1234
123,2012,0
123,2012,1
124,2010,12345
124,2010,0
124,2010,0
124,2011,12445
124,2011,0
124,2011,1
124,2012,13444
124,2012,0
124,2012,0
run;

data after;
infile datalines dlm=',';
input policy_no  year  vehicle_nos 5. ;
datalines ;
123,2010,1233
123,2010,1234
123,2010,1234
123,2011,1234
123,2011,1234
123,2011,1235
123,2011,1235
123,2011,1239
123,2012,1234
123,2012,1234
123,2012,1235
124,2010,12345
124,2010,12345
124,2010,12345
124,2011,12345
124,2011,12345
124,2011,12346
124,2012,13444
124,2012,13444
124,2012,13444
run;

Accepted Solutions
Solution
‎08-31-2015 07:34 AM
Super User
Posts: 10,239

## Re: Retaining

data after (drop=vno);

set before (rename=(vehicle_nos=vno));

by policy_no year;

retain vehicle_nos;

if first.year

then vehicle_nos = vno;

else vehicle_nos = vehicle_nos + vno;

run;

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code

All Replies
Solution
‎08-31-2015 07:34 AM
Super User
Posts: 10,239

## Re: Retaining

data after (drop=vno);

set before (rename=(vehicle_nos=vno));

by policy_no year;

retain vehicle_nos;

if first.year

then vehicle_nos = vno;

else vehicle_nos = vehicle_nos + vno;

run;

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
🔒 This topic is solved and locked.

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

Discussion stats