Help using Base SAS procedures

copy previous data

Reply
Frequent Contributor
Frequent Contributor
Posts: 79

copy previous data

Hi,

I have a dataset called security

dates                     security      index

04/04/2012               A               1.12

04/05/2012               A               1.13

04/06/2012               A               1.14

04/09/2012               A               1.15

04/05/2012               B               1

04/09/2012               B               1.04 

Hello, I am looking to fill in the missing data for the above table for example, for each security I would look at what the min and the max date is and fill it with all bus dates in the middle and if there is any missing date I would fill it with the previous available data so for example the above set would look like

dates                     security      index

04/04/2012               A               1.12

04/05/2012               A               1.13

04/06/2012               A               1.14

04/09/2012               A               1.15

04/05/2012               B               1

04/06/2012               B               1

04/09/2012               B               1.04  

I would not add the 04/04/2012 for B. I am not really sure what syntax I should be using for this any help is appreciated thanks!

Respected Advisor
Posts: 3,777

Re: copy previous data

Some call this LOCF in PROC EXPAND it is call step interpolation.  You will need to define BUS day.  I used Mon-Fri. but you may want to account for holidays SAS has some tools to help with that.

data sec;
   input date:mmddyy. security:$1.  index;
   format date date9.;
  
cards;
04/04/2012               A               1.12
04/05/2012               A               1.13
04/06/2012               A               1.14
04/09/2012               A               1.15
04/05/2012               B               1
04/09/2012               B               1.04 
;;;;
   run;
proc summary data=sec nway;
  
class sec:;
   output out=mm(drop=_type_) min(date)=min max(date)=max;
  
run;
data frame(keep=security date) / view=frame;
   set mm;
   do date = min to max;
      if weekday(date) in(2:6) then output;
     
end;
  
format date date9.;
  
run;
data filled / view=filled;
   merge sec frame;
   by security date;
   run;
data locf;
   update filled(obs=0) filled;
   by security;
   output;
  
run;
     
Ask a Question
Discussion stats
  • 1 reply
  • 138 views
  • 0 likes
  • 2 in conversation