topic Re: Missing values from previous dates in SAS Data Management

Missing values from previous dates

MalikShahbazz — Thu, 13 Jun 2019 20:22:50 GMT

Hello,

I have some data stored in SAS in the following form -

Txn ID	Month	Value
1	201811	120
1	201812	240
1	201902	350
1	201903	400
1	201905	100
2	201812	200
2	201902	300

I essentially want to fill up the missing month values until say May of this year, this is the data that I want -

Txn ID	Month	Value
1	201811	120
1	201812	240
1	201901	24
1	201902	350
1	201903	400
1	201904	400
1	201905	100
2	201812	200
2	201901	200
2	201902	300

Could someone help me with this , please!

Re: Missing values from previous dates

Reeza — Thu, 13 Jun 2019 20:57:09 GMT

PROC TIMESERIES or PROC EXPAND are good options.

Re: Missing values from previous dates

novinosrin — Thu, 13 Jun 2019 23:25:49 GMT

Hi @Reeza Hope you are well. Thank you so much for the mention in the other thread and for the response in the thread I initiated. As always your kindness speaks volumes. I will PM in regards to the change in situation in my life(slowdown here 😞 ) having to move to Bridgeport, CT to pursue a new career opportunity. Loving CT though. @ChrisHemedinger 's help hopefully should get me back in full swing. Take care & See ya soon!

@MalikShahbazz If you do not have ETS

data have;
input TxnID	Month : yymmn6.	Value;
format Month  yymmn6.;
cards;
1	201811	120
1	201812	240
1	201902	350
1	201903	400
1	201905	100
2	201812	200
2	201902	300
;

proc means data=have nway noprint;
class txnid;
var month;
output out=temp(drop=_type_ _freq_) min=_min max=_max;
run;

data want ;
if _n_=1 then do;
  if 0 then set have;
   dcl hash H (dataset:'have') ;
   h.definekey  ("txnid","month") ;
   h.definedata ("value") ;
   h.definedone () ;
   end;
set temp;
month=_min;
do while(month<=_max);
_rc=h.find();
output;
month=intnx('month',month,1);
end;
drop _:;
run;

Of course I could do it one step, however the purpose for lazy folks like me to avoid gymnastics along with retain and parking. Cheers!

Re: Missing values from previous dates

Ksharp — Fri, 14 Jun 2019 12:38:58 GMT

Thanks @novinosrin 's dataset .

data have;
input TxnID	Month : yymmn6.	Value;
format Month  yymmn6.;
cards;
1	201811	120
1	201812	240
1	201902	350
1	201903	400
1	201905	100
2	201812	200
2	201902	300
;

data want;
 merge have have(keep=txnid month rename=(txnid=_id month=_month) firstobs=2);
 output;
 if txnid=_id then do;
   do i=1 to intck('month',month,_month)-1;
    month=intnx('month',month,1);output;
   end;
 end;
drop _: i;
run;