Date format in sas concatenate with a proc sql under conditions

Question

i have a macro_variable :%let date=201909 and table :

ID   Sending-date  item      
 1   15-jul-2019    A   
 2   23-sep-2019    B  
 3   12-sep-2019    A        
 4    1-jan-2019    B  
 5   5-feb-2019     B       

What i'm wondering to do is to verify if there is an item sent in the month indicated in my date (09 september) and the two previous months (august and july) using a proc sql and without adding new variables.
the result_table expected is like this :

Month   Year  Number of items
9       2019        2
8       2019        0 
7       2019        1

The biggest problem is how to convert the format of the date in the table like my macro_variable date.

Answer 1

Here's one method.

I used cutoff_date instead of date, because it helps differentiate the dates more easily. Use INTNX() to do date calculations. In this case, I set the cutoff to be the end of cutoff_month and the start of two months prior. You may need to define that a bit more clearly to meet your needs but this works.

%let cutoff_date=201909; 

 proc sql;
 create table want as
 select month(sending_date) as Month, count(*) as num
 from have
 where sending_date between intnx('month', input("&cutoff_date.", yymmn6.), 0, 'e') and intnx('month', input("&cutoff_date.", yymmn6.), -2, 'b')
 group by calculated Month;
 quit;