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);
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?
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.
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:
@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.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: