copy previous data

hdg — Thu, 14 Feb 2013 23:53:34 GMT

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!

Re: copy previous data

data_null__ — Fri, 15 Feb 2013 00:36:06 GMT

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; 
      

topic copy previous data in SAS Procedures

copy previous data

Re: copy previous data