topic Re: Running a Macro for a control table of dates in SAS Programming

Running a Macro for a control table of dates

chimanbj — Tue, 20 Sep 2016 20:18:28 GMT

I'm trying to run a macro that gives counts of widgets by date. The macro give me a count of scheduled deliverables and a count of acutal deliverables for a given day. I want to loop through this macro "N" times, based on the dates that are in a separate table. This table would contain dates for the year that are work days, excluding holidays and weekends.

Ultimately, I'd like to set up the date table such that I have dates for the next few years, and set the code so that it controls for when the date in the control table doesn't exceed the current date.

Here's the macro as it currently stands:

%macro takt(rptdate);
proc sql;
create table takt_data as
select "&rptdate" as report_date,
        sum(ship_date = input("&rptdate",mmddyy10.)) as actual,
       sum(contract_date = input("&rptdate",mmddyy10.)) as planned,
       calculated actual / calculated planned as takt_index
from shippinglog
where substr(project,1,2) <> "CD"
;
quit;
proc append base = takt data = takt_data force;
quit;
%mend takt;
%takt(09/01/2016);
%takt(09/02/2016);
%takt(09/05/2016);
%takt(09/06/2016);
%takt(09/07/2016);
%takt(09/08/2016);
%takt(09/09/2016);
%takt(09/12/2016);
%takt(09/13/2016);
%takt(09/14/2016);
%takt(09/15/2016);
%takt(09/16/2016);
%takt(09/19/2016);
%takt(09/20/2016);

Re: Running a Macro for a control table of dates

Astounding — Tue, 20 Sep 2016 20:47:10 GMT

Why do you need a separate table of working days? Would your shipping log ever contain dates that are weekends or holidays? If it did, would you want to ignore them?

It certainly seems like this could be done in one step, rather than repeating one step per day. But we'll get to that later. First, what's in the shipping log?

Re: Running a Macro for a control table of dates

chimanbj — Tue, 20 Sep 2016 20:50:52 GMT

The shipping log is a contains a list of projects that have been delivered, as well as projects that are currently being worked on. I need to be able to see weekdays where no date occurred. I'm looking at two date fields, ship_date (actual deliveries) and contract_date (expected deliveries). There are some dates where I don't have an actual or expected. Those dates need to be tracked in my report as well as the dates where we have activity.

Re: Running a Macro for a control table of dates

Reeza — Tue, 20 Sep 2016 20:55:25 GMT

Basically you should provide some sample data, not necessarily real, that mimics your actual data and expected results. Then we can help with suggesting methods that may work.

Re: Running a Macro for a control table of dates

Astounding — Tue, 20 Sep 2016 20:55:40 GMT

OK, so one plan would be to use the shipping log just once. No macros involved. Get a summary for each day in the shipping log.

Having done that, merge it with your list of days of interest. As part of that step, you can:

Remove any shipping dates that are not on your list of dates.
Set summary variables to 0 for dates that are not in your shipping log.

Re: Running a Macro for a control table of dates

Reeza — Tue, 20 Sep 2016 21:07:42 GMT

Run a proc freq of contract vs actual date -> save to a dataset

Keep only records where contract = actual date (where actual_date=contract_date)

These will be your % takt_index

1- values is missed

Check the percent values for the one that matches your requirements.

proc freq data=have noprint;

table ship_date*contract_date/out=delivery (where=(ship_date=contract_date)) outpct;

run;

Re: Running a Macro for a control table of dates

ballardw — Tue, 20 Sep 2016 22:14:51 GMT

@chimanbj wrote:
The shipping log is a contains a list of projects that have been delivered, as well as projects that are currently being worked on. I need to be able to see weekdays where no date occurred. I'm looking at two date fields, ship_date (actual deliveries) and contract_date (expected deliveries). There are some dates where I don't have an actual or expected. Those dates need to be tracked in my report as well as the dates where we have activity.

Sounds almost like Select distinct from the shipping log and compare the control data set except the dates in that distinct list. Below the code has a distict set of shipping log dates and compared with those in a control set. If you have a specific product requirement then including that with the selects would work.

data shippinglog;
   informat x mmddyy10.;
   format x mmddyy10.;
   input x;
datalines;
05/15/2016
05/16/2016
05/17/2016
05/18/2016
05/20/2016
;
run;

Data control ;
   informat x mmddyy10.;
   format x mmddyy10.;
   input x;
datalines;
05/15/2016
05/16/2016
05/17/2016
05/18/2016
05/19/2016
05/20/2016
;
run;

proc sql;
   select x from control
   except
   select x from shippinglog;
quit;

The second select might even be" select distinct x from fullshippinglog;

The results are the ones in Control that are NOT in the shipping log.