I have table with many id1 and id2 pairs
id1 id2 date value
7899 11774 31MAR2010 10
7899 11774 30APR2010 20
7899 11774 30JUN2010 40
7899 11774 31AUG2010 60
8110 45987 31DEC2010 100
......
Then I want to add 10 months for every id1 and id2 pair from the starting date and on. If a value is missing I want it to use the previous months value. The resulting table will then look like this.
id1 id2 date value
7899 11774 31MAR2010 10
7899 11774 30APR2010 20
7899 11774 31MAY2010 20
7899 11774 30JUN2010 40
7899 11774 31JUL2010 40
7899 11774 31AUG2010 60
7899 11774 30SEP2010 60
7899 11774 31OKT2010 60
7899 11774 30NOV2010 60
7899 11774 31DEC2010 60
8110 45987 31DEC2010 100
8110 45987 31JAN2011 100
8110 45987 28FEB2011 100
8110 45987 31MAR2011 100
8110 45987 30APR2011 100
8110 45987 31MAY2011 100
8110 45987 30JUN2011 100
8110 45987 31JUL2011 100
8110 45987 31AUG2011 100
8110 45987 30SEP2011 100
Ideas how to do this?
data have;
input id1 id2 date:date9. value;
cards;
7899 11774 31MAR2010 10
7899 11774 30APR2010 20
899 11774 30JUN2010 40
7899 11774 31AUG2010 60
8110 45987 31DEC2011 100
;
data want;
set have;
output;
if intnx('month',date,1,'e') ne . and intnx('month',date,1,'e') gt date then do;
date=intnx('month',date,1,'e');
output;
end;
format date date9.;
run;
proc sort data=want nodupkey;
by date;
run;
Almost but I suppose I wasn't clear about one important thing. I want to be able to set how many time periods to extrapolate. I wrote in my example 10 months. But this I want to be able to change to let's say 20, 40 or whatever. So for the 10 month example, the result should look like this.
The have table
I have table with many id1 and id2 pairs
id1 id2 date value
7899 11774 31MAR2010 10
7899 11774 30APR2010 20
7899 11774 30JUN2010 40
7899 11774 31AUG2010 60
8110 45987 31DEC2010 100
The resulting table
id1 id2 date value
7899 11774 31MAR2010 10
7899 11774 30APR2010 20
7899 11774 31MAY2010 20
7899 11774 30JUN2010 40
7899 11774 31JUL2010 40
7899 11774 31AUG2010 60
7899 11774 30SEP2010 60
7899 11774 31OKT2010 60
7899 11774 30NOV2010 60
7899 11774 31DEC2010 60
8110 45987 31DEC2010 100
8110 45987 31JAN2011 100
8110 45987 28FEB2011 100
8110 45987 31MAR2011 100
8110 45987 30APR2011 100
8110 45987 31MAY2011 100
8110 45987 30JUN2011 100
8110 45987 31JUL2011 100
8110 45987 31AUG2011 100
8110 45987 30SEP2011 100
data have;
input id1 id2 date:date9. value;
format date date9.;
cards;
7899 11774 31MAR2010 10
7899 11774 30APR2010 20
7899 11774 30JUN2010 40
7899 11774 31AUG2010 60
8110 45987 31DEC2011 100
;
data want;
merge have have(keep=id1 id2 date
rename=(id1=_id1 id2=_id2 date=_date) firstobs=2);
output;
if id1=_id1 and id2=_id2 then do;
do i=1 to intck('month',date,_date)-1;
date=intnx('month',date,1,'e');output;
end;
end;
drop _: i;
run;
Sorry, but I made a small typing error, for the 10 months example (the 10 should be easily changed if required in the code) the results looks like this.
The resulting table
id1 id2 date value
7899 11774 31MAR2010 10
7899 11774 30APR2010 20
7899 11774 31MAY2010 20
7899 11774 30JUN2010 40
7899 11774 31JUL2010 40
7899 11774 31AUG2010 60
7899 11774 30SEP2010 60
7899 11774 31OKT2010 60
7899 11774 30NOV2010 60
7899 11774 31DEC2010 60
8110 45987 31DEC2010 100
8110 45987 31JAN2011 100
8110 45987 28FEB2011 100
8110 45987 31MAR2011 100
8110 45987 30APR2011 100
8110 45987 31MAY2011 100
8110 45987 30JUN2011 100
8110 45987 31JUL2011 100
8110 45987 31AUG2011 100
8110 45987 30SEP2011 100
That would be more simple.
data have;
input id1 id2 date : date9. value;
format date date9.;
cards;
7899 11774 31MAR2010 10
7899 11774 30APR2010 20
7899 11774 30JUN2010 40
7899 11774 31AUG2010 60
8110 45987 31DEC2010 100
;
%let n=10;
proc summary data=have ;
by id1 id2;
var date;
output out=temp min=;
run;
data temp1;
set temp;
output;
do i=1 to %eval(&n-1);
date=intnx('month',date,1,'e');output;
end;
drop i _: ;
run;
data want;
merge temp1(in=ina) have;
by id1 id2 date;
retain new_value;
if first.id2 then call missing(new_value);
if not missing(value) then new_value=value;
if ina;
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.