topic Re: Count for a specific date range in New SAS User

Count for a specific date range

aperansi — Tue, 27 Nov 2018 20:09:27 GMT

I have a dataset that I created that pulls in data over a 13 month period. I am pulling a list of unique keys and dates, and I am getting a count of the keys within a given day.

The issue im running into i that I need the count to express the count of api_calls that happen in a month and go from the last Saturday of the previous month, to the last Friday of the current month. The current month should be the month where the count is recorded.

For example: I have an invoice for August. The start date of that invoice would be July 28, 2018 (Last Saturday of the Month) to August 31, 2018 (Last Friday of the current month). The count for August should reflect the above period. And all other months should be consistent with this as well.

Could someone please help me figure out how to code this into my program?

Re: Count for a specific date range

pink_poodle — Tue, 06 Nov 2018 22:18:11 GMT

A possible start is to express a date in a Month-day-week format

that has month ( 1 - 12 ), week (1 - 4), day (1 - 7)

Then assign variables month, week, day based on the formatted dates

group by month, week, day

(a) count whole month, (b) count last friday to the end of the month, (c) count last saturday of previous month to the end of that month.

final count = (a) - (b) + (c)

Re: Count for a specific date range

FreelanceReinh — Wed, 07 Nov 2018 00:20:43 GMT

Hi @aperansi,

You can adapt the solution in the earlier thread to the new period definition. For example, the "invoice month" for a given api_date (e.g. '2018-08' for api_date='30JUL2018'd) can be written as

put(intnx('month',api_date,api_date>nwkdom(5,6,month(api_date),year(api_date))),yymmd7.)

Re: Count for a specific date range

aperansi — Wed, 07 Nov 2018 16:16:36 GMT

Im having trouble working through this in my head. Can you help me understand this a little more?

Re: Count for a specific date range

FreelanceReinh — Wed, 07 Nov 2018 18:12:25 GMT

Sure. Let's analyze the expression from the inside outwards:

The MONTH and YEAR functions are applied to the SAS date value in api_date to obtain month and year of the respective date. These are the third and fourth argument of function NWKDOM.
The first two arguments of NWKDOM, 5 and 6, stand for the fifth (or last) Friday (=sixth day of the week in SAS) of the month specified in the other two arguments. So, nwkdom(5,6,month(api_date),year(api_date)) is the SAS date value of the last Friday of the month api_date falls into. (As described in the documentation of NWKDOM, the 5 in the first argument works even in cases where the last Friday is only the fourth Friday of the month.)
The inequality api_date>nwkdom(...) evaluates to either 1 (true) or 0 (false) depending on whether api_date falls after the last Friday of the month or not. So, the first argument of the PUT function amounts to either intnx('month',api_date,1) or intnx('month',api_date,0).
The INTNX function in turn computes a SAS date value from the next month (third argument 1) or the same month (third argument 0) api_date falls into, namely the beginning of the respective month (see default value of the fourth argument, 'alignment', in the documentation of INTNX).
Finally, the PUT function converts the SAS date value obtained by INTNX into a seven-character string of the form YYYY-MM, e.g. 21397 (='01AUG2018'd) into '2018-08'.*

The result is what you desired as the month of invoice: The "next" month if api_date falls after the last Friday of the month and the "current" month otherwise. You can use this expression in a GROUP BY clause as was suggested by DBailey in the other thread (calculated period). The other two expressions in the GROUP BY clause can be defined in a similar way.

*Edit: Feel free to use a different format in place of yymmd7., e.g. monyy7., as long as it aggregates SAS date values on month-year level.

Re: Count for a specific date range

aperansi — Wed, 07 Nov 2018 18:20:33 GMT

Thank you for providing that clarification! What a great explanation! Im just still confused on how I would get the PeriodStart and Period End for what I need, using the intnx statement. Could you assist me with that?

Re: Count for a specific date range

DBailey — Wed, 07 Nov 2018 18:43:22 GMT

Re: Count for a specific date range

FreelanceReinh — Wed, 07 Nov 2018 20:21:32 GMT

Okay. Again, we have to distinguish between two cases:

api_date falls after the last Friday of the month.
In this case PeriodStart is the last Saturday of the same month or, equivalently, the day after the last Friday of that month.

PeriodEnd is the last Friday of the next month.
api_date does not fall after the last Friday of the month.
In this case PeriodStart is the day after the last Friday of the previous month. (Note that there was an ambiguity in your definition where you wrote "last Saturday of the previous month"! Your example of August 2018, however, resolved this ambiguity: Instead of "last Saturday of the previous month" you meant "the day after the last Friday of the previous month" [which can be the first day of the current month!]. Otherwise, the last few days of August 2018 would appear again in the September invoice!)

PeriodEnd is the last Friday of the current month.

So, we recognize a common pattern:

PeriodStart = nwkdom(5,6,m,y)+1 for certain month-year combinations (m, y) depending on the case. More precisely:

m=month(intnx('month', api_date, (api_date>nwkdom(5,6,month(api_date),year(api_date)))-1))
y= year(intnx('month', api_date, (api_date>nwkdom(5,6,month(api_date),year(api_date)))-1))

So, the final expression for PeriodStart can be written as

nwkdom(5,6,month(intnx('month', api_date, (api_date>nwkdom(5,6,month(api_date),year(api_date)))-1)),year(intnx('month', api_date, (api_date>nwkdom(5,6,month(api_date),year(api_date)))-1)))+1

An equivalent and perhaps better readable CASE expression (assuming you want to use PROC SQL) is:

case when api_date>nwkdom(5,6,month(api_date),year(api_date)) then nwkdom(5,7,month(api_date),year(api_date))
     else nwkdom(5,6,month(intnx('month',api_date,-1)),year(intnx('month',api_date,-1)))+1
end

(Note that I use nwkdom(5,7,...), i.e. last Saturday in one place, as explained above.)

Similarly, PeriodEnd can be written as

nwkdom(5,6,month(intnx('month', api_date, api_date>nwkdom(5,6,month(api_date),year(api_date)))),year(intnx('month',api_date,api_date>nwkdom(5,6,month(api_date),year(api_date)))))

or, equivalently, as

case when api_date>nwkdom(5,6,month(api_date),year(api_date)) then nwkdom(5,6,month(intnx('month',api_date,1)),year(intnx('month',api_date,1)))
     else nwkdom(5,6,month(api_date),year(api_date))
end

All these expressions are quite lengthy. Maybe there is a more elegant way to define these non-standard intervals as custom intervals which can be used in the INTNX (and INTCK) function (see documentation).

And definitely there are other ways to compute the (invoice-)monthly totals. Here is an example:

/* Create a format to assign the "invoice month" to a given date */

data invmonf / view=invmonf;
fmtname='invmonf';
do start='01AUG2017'd to '31DEC2020'd; /* modify the range as appropriate */
  label=put(intnx('month',start,start>nwkdom(5,6,month(start),year(start))),yymmd7.);
  output;
end;
format start weekdate.;
run;

proc format cntlin=invmonf;
run;

/* Compute total counts for each "invoice month" */

proc summary data=have nway;
format api_date invmonf.;
class api_date;
var count;
output out=mtotals(drop=_: rename=(api_date=invoice_month)) sum=;
run;

proc print data=mtotals;
run;

Re: Count for a specific date range

FreelanceReinh — Wed, 07 Nov 2018 20:29:59 GMT

Interesting, I've just noticed that @DBailey had posted another reply. A quick comparison of the results (for made-up input data) has revealed that they differ! I leave it to you, @aperansi, to decide which solution is correct for your purposes. (I'd use your example, the invoice for August 2018, to find out.)