## Unrolling date data

Solved
Frequent Contributor
Posts: 126

# Unrolling date data

My data currently looks like this:

LoanID    Cycle_Year    Default_Year  Payoff_Year

54364          2008                2008             .

54364          2010                2010             .

76943          2008                2008             .

76943          2012                  .              2012

Is there any way that I can "unroll" my data to look like this? Basically, I need the full spectrum of years from 2006 to 2012.

LoanID    Cycle_Date    Default_Date  Payoff_Date

54364           2006                 .                      .

54364           2007                 .                      .

54364           2008              2008                  .

54364           2009                 .                      .

54364           2010              2010                  .

54364           2011                 .                      .

54364           2012                 .                      .

76943           2006                 .                      .

76943           2007                 .                      .

76943           2008              2008                  .

76943           2009                  .                     .

76943           2010                  .                     .

76943           2011                  .                     .

76943           2012                  .                  2012

Accepted Solutions
Solution
‎09-25-2015 06:23 AM
Super User
Posts: 10,283

## Re: Unrolling date data

%let start=2006;

%let end=2012;

data have;
infile cards;
input loanid cycle_year default_year payoff_year;
cards;
54364 2008 2008 .
54364 2010 2010 .
76943 2008 2008 .
76943 2012 . 2012
;
run;

data int (keep=loanid cycle_date); *create all years from every input record;

set have;

do cycle_date = &start to &end;

output;

end;

run;

proc sql;

create table want as

select distinct a.loanid, a.cycle_date, b.default_year as default_date, b.payoff_year as payoff_date

from int a left join have b on a.loanid = b.loanid and a.cycle_date = b.cycle_year;

quit;

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

All Replies
Solution
‎09-25-2015 06:23 AM
Super User
Posts: 10,283

## Re: Unrolling date data

%let start=2006;

%let end=2012;

data have;
infile cards;
input loanid cycle_year default_year payoff_year;
cards;
54364 2008 2008 .
54364 2010 2010 .
76943 2008 2008 .
76943 2012 . 2012
;
run;

data int (keep=loanid cycle_date); *create all years from every input record;

set have;

do cycle_date = &start to &end;

output;

end;

run;

proc sql;

create table want as

select distinct a.loanid, a.cycle_date, b.default_year as default_date, b.payoff_year as payoff_date

from int a left join have b on a.loanid = b.loanid and a.cycle_date = b.cycle_year;

quit;

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