BookmarkSubscribeRSS Feed
chimanbj
Obsidian | Level 7

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);

6 REPLIES 6
Astounding
PROC Star

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?

chimanbj
Obsidian | Level 7
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.


Reeza
Super User

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. 

Astounding
PROC Star

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.
ballardw
Super User

@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.

Reeza
Super User

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;

 

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 1480 views
  • 1 like
  • 4 in conversation