topic Use previous value if the date is missing in SAS Programming

Use previous value if the date is missing

rhapsody — Thu, 20 Jun 2019 07:34:20 GMT

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?

Re: Use previous value if the date is missing

Jagadishkatam — Wed, 19 Jun 2019 09:06:05 GMT

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;

Re: Use previous value if the date is missing

Ksharp — Wed, 19 Jun 2019 12:40:24 GMT

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;

Re: Use previous value if the date is missing

rhapsody — Thu, 20 Jun 2019 07:33:01 GMT

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

Re: Use previous value if the date is missing

rhapsody — Thu, 20 Jun 2019 07:29:26 GMT

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

Re: Use previous value if the date is missing

Ksharp — Thu, 20 Jun 2019 12:28:19 GMT

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;