Add records to existing dataset based on variables within the data

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

Add records to existing dataset based on variables within the data

 

 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                                                         


Accepted Solutions
Solution
‎06-06-2017 10:58 AM
Trusted Advisor
Posts: 1,568

Re: Add records to existing dataset based on variables within the data

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


All Replies
Super User
Posts: 19,815

Re: Add records to existing dataset based on variables within the data

Check out PROC TIMESERIES. 

PROC Star
Posts: 752

Re: Add records to existing dataset based on variables within the data

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?

New Contributor
Posts: 3

Re: Add records to existing dataset based on variables within the data

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.        

Trusted Advisor
Posts: 1,568

Re: Add records to existing dataset based on variables within the data

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;



        
Solution
‎06-06-2017 10:58 AM
Trusted Advisor
Posts: 1,568

Re: Add records to existing dataset based on variables within the data

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;
New Contributor
Posts: 3

Re: Add records to existing dataset based on variables within the data

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 172 views
  • 3 likes
  • 4 in conversation