Unrolling date data

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 120
Accepted Solution

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
Esteemed Advisor
Posts: 6,698

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

View solution in original post


All Replies
Solution
‎09-25-2015 06:23 AM
Esteemed Advisor
Posts: 6,698

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
☑ This topic is SOLVED.

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

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