BookmarkSubscribeRSS Feed
hdg
Obsidian | Level 7 hdg
Obsidian | Level 7

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!

1 REPLY 1
data_null__
Jade | Level 19

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;
     

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 1 reply
  • 593 views
  • 0 likes
  • 2 in conversation