topic Re: Add records to existing dataset based on variables within the data in SAS Programming

Add records to existing dataset based on variables within the data

Bill_S3 — Tue, 06 Jun 2017 13:11:01 GMT

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

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

Reeza — Tue, 06 Jun 2017 13:45:01 GMT

Check out PROC TIMESERIES.

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

PeterClemmensen — Tue, 06 Jun 2017 14:27:47 GMT

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?

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

Bill_S3 — Tue, 06 Jun 2017 14:40:11 GMT

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.

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

Shmuel — Tue, 06 Jun 2017 14:42:34 GMT

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;

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

Shmuel — Tue, 06 Jun 2017 14:51:57 GMT

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;

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

Bill_S3 — Tue, 06 Jun 2017 14:59:37 GMT

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