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
%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;
%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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: