Getting a count for a specific date range

[aperansi]

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 when getting a count is that I need the count for each month to go from the 25th through the 25th of every month. 

For example: I need a count of all of the keys from Jan 25th 2018 through February 25th 2018, and I need the data step in sas to recognize this as the count for the month of February.  This pattern continues every month going forward.

.

[PaigeMiller]

The issue im running into when getting a count is that I need the count for each month to go from the 25th through the 25th of every month. 

This seems like an incomplete explanation. What is stopping you from obtaining this count?

and I need the data step in sas to recognize this as the count for the month of February.

Find the maximum date in the data step and assign the month of that maximum date to a variable in this data set. Or, do this in PROC SQL when you extract the data.

[aperansi]

What's stopping me is I dont know how to code that within my current program. I am able to get the count for all records within a given month, I just dont know how to get a count when I have data within two months, and need the output to reflect that it occurred in one month.

Thank you for your input.

[Astounding]

Here's some logic you can use, to create a new variable:

if day(api_date) < 25 then counting_date = intnx('month', api_date, -1);

else counting_date = intnx('month', api_date, 0);

format counting_date yymmd7.;

The new variable is a the first day of the month that should be used to count that observation.  The format statement prints that variable in a suitable format, such as:

2018-02

It's not clear if you meant < 25 or <= 25.  I don't think you would want the 25th day of the month to be counted in two different months.

[DBailey · Accepted solution]

You should be able to modify this to meet your needs...

data have;
input api_date:mmddyy10. api_count;
format api_date mmddyy10. api_count 8.0;
datalines;
1/1/2017 125
8/2/2017 126
8/3/2017 127
8/4/2017 128
8/5/2017 129
8/6/2017 130
8/7/2017 131
8/8/2017 132
8/9/2017 133
8/10/2017 134
8/11/2017 135
8/12/2017 136
8/13/2017 137
8/14/2017 138
8/15/2017 139
8/16/2017 140
8/17/2017 141
8/18/2017 142
8/19/2017 143
8/20/2017 144
8/21/2017 145
8/22/2017 146
8/23/2017 147
8/24/2017 148
8/25/2017 149
8/26/2017 150
8/27/2017 151
8/28/2017 152
8/29/2017 153
8/30/2017 154
8/31/2017 155
9/1/2017 156
9/2/2017 157
9/3/2017 158
9/4/2017 159
9/5/2017 160
9/6/2017 161
;
run;

proc sql;
create table want as select		
	intnx('month',api_date,case when day(api_date)>=25 then 1 else 0 end,'begin') format mmddyy10. as  Period,
	intnx('month',api_date,case when day(api_date)>=25 then 0 else - 1 end,'begin')+24 format mmddyy10. as PeriodStart,
	intnx('month',api_date,case when day(api_date)>=25 then 1 else 0 end,'begin')+23 format mmddyy10. as PeriodEnd
	,sum(api_count) as Total_API_Count
from	
	have
group by
	calculated period
	,calculated periodstart
	,calculated periodEnd
;
quit;
	

Here's the output:

01/01/2017 12/25/2016 01/24/2017 125
08/01/2017 07/25/2017 08/24/2017 3151
09/01/2017 08/25/2017 09/24/2017 2015

[aperansi]

So I altered this for my needs, and I am running into some issues. The new tablet isnt bringing in the mid, product, and vendor so I added that into the code.  The total API Counts for each individual product however seem to be off for the given period. Do you have a recommendation for this?

Here is an image of the output from what you gave me.

[NanLiu]

Use proc format like follow can solve your problem easily.

For example:

proc format;

value NewMonth '05AUG2017'd - '05SEP2017'd= "August"

other = "Other";

run;

data test;

date='01AUG2018'd;

date2='09AUG2017'd;

month=put(date,newmonth.);

month2=put(date2,newmonth.);

run;

proc print; run;

[DBailey]

Did you add the mid/product/vendor to the group by statement?

[aperansi]

No, I added it after the as Total_API_Count

[DBailey]

if you want the api count to be group by those values, then you'd need to include them in the group by statement.  If not, then I'm not seeing what's wrong.

[aperansi]

Would you mind breifly explaining the logic around your original code so that I can have a better understanding of whats going on? I would greatly appreciate that. Thank you Mr. Bailey.

[DBailey]

So INTNX('month',......) is used to move dates by a certain # of months.  It also can adjust to the beginning, ending, or the sameday in that new month. 

intnx('month',api_date,case when day(api_date)>=25 then 1 else 0 end,'begin') format mmddyy10. as Period,

I understood that if the api_date was between the 25th of the previous month and the 24th of the current month, that data was to be tagged as the first of the current month.  That can be translated to "if the api_date is 25 or greater, move to the next month otherwise keep the current month" which is used in the intnx function as the # of months to move and use the beginning of that resulting month.

intnx('month',api_date,case when day(api_date)>=25 then 0 else - 1 end,'begin')+24 format mmddyy10. as PeriodStart,

If the api_date >= 25, move to the beginning of the current month and add 24 days (ends up on the 25th).  If the api_date < 25, move to the beginning of the prior month and add 24 days (resulting in the 25th of the prior month).

intnx('month',api_date,case when day(api_date)>=25 then 1 else 0 end,'begin')+23 format mmddyy10. as PeriodEnd 

if the api_date >= 25, move to the beginning of the next month and add 23 days (ends up on 24th of next month).  If the api_date < 25, move to the beginning of current month and add 23 days (ends up on 24th of current month).