09-20-2016 04:18 PM
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:
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
where substr(project,1,2) <> "CD"
proc append base = takt data = takt_data force;
09-20-2016 04:47 PM
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?
09-20-2016 04:50 PM
09-20-2016 04:55 PM
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.
09-20-2016 04:55 PM
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:
09-20-2016 06:14 PM
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.
09-20-2016 05:07 PM
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;