BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Bill_S3
Fluorite | Level 6
 

 Here is an example of the data where there is a variable                   
that holds a date, and the corresponding amount.                           
                                                                           
Date_var        Amount                                                     
05/15/2017      150                                                        
05/16/2017      175                                                        
05/19/2017      190                                                        
05/20/2017      165                                                        
                                                                           
                                                                           
As you can see, the weekend values are missing.  How can I                 
create additional records to this dataset that fill in the                 
missing weekend dates, where the Amount will be the same as                
the previous business                                                      
day that was populated?   The data set should look like this when complete.
                                                                           
Date_var        Amount                                                     
05/15/2017      150                                                        
05/16/2017      175                                                        
05/17/2017      175                                                        
05/18/2017      175                                                        
05/19/2017      190                                                        
05/20/2017      165                                                         

1 ACCEPTED SOLUTION

Accepted Solutions
Shmuel
Garnet | Level 18
A variation of ELSE DO - to output even if not weekend:

else do;
amount = lag_qty;
output;
if weekday not in (6, 7) then
put '>>> ' Date_var= date9. weekday=; end;

View solution in original post

6 REPLIES 6
Reeza
Super User

Check out PROC TIMESERIES. 

PeterClemmensen
Tourmaline | Level 20

What if the missing date was not a weekend date? Should you leave out that record or still add this date with the amount of the previous record to the dataset?

Bill_S3
Fluorite | Level 6

If the date was missing, would always want to fill with the previous day amount.   Valid business days will always have a date record populated, even if the Amount variable is $0.  Only Bank holidays and weekends will be missing.    I need something that will fill in the missing dates, and carry over the amount from the previous business day.  Thanks.        

Shmuel
Garnet | Level 18

You can use next code which checks absent dates are they realy weekend or not

data test;
   infile cards;
   input date_var mmddyy10. amount;
   format date_var date9.;
cards;
05/15/2017 150
05/16/2017 175 
05/19/2017 190 
05/20/2017 165
;
run;

proc means data=test;
  class;
  var date_var;
  output out=min_max min=min_dt max=max_dt;
run;

data all_days;
 set min_max;
     keep date_var weekday;
     do date_var = min_dt to max_dt;
        weekday = weekday(date_var);
        output;
     end;
run;

data want;
 merge all_days test;
 by date_var;
    retain amount lag_qty;
    if not missing(amount) then do;
       lag_qty = amount;
       output;
    end;
    else do;
       if weekday in (6, 7) then do;  /* weekend days */
          amount = lag_qty;
          output;
       end;
       else put '>>> ' Date_var= date9.  weekday=;
    end;
run;



        
Shmuel
Garnet | Level 18
A variation of ELSE DO - to output even if not weekend:

else do;
amount = lag_qty;
output;
if weekday not in (6, 7) then
put '>>> ' Date_var= date9. weekday=; end;
Bill_S3
Fluorite | Level 6

Thanks so much.   Your second "else do" worked perfectly.   Appreciate the help!

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 6 replies
  • 700 views
  • 3 likes
  • 4 in conversation