SAS Where Clause with Macro Variables

Question

This is most likely a simple question, but I have not yet been able to figure it out.

I need to retrieve some data from a SAS dataset where the DATE falls within a 6 month range (ex. 01JAN2017 to 30JUN2017) I tried running the following code, but it results in an error. Any thoughts? I'm sure this is something simple...

%let start1 = %Sysfunc( InputN( 01JAN2017 , Date9. ) ) ;
%let start2 = %sysfunc(putN(&start1, date9));
%put start1 &start1 start2 &start2;

%let end1 = %sysfunc(inputn(30JUN2017,Date9.));
%let end2 = %sysfunc(putN(&end1, date9));
%put end1 &end1 end2 &end2;

proc print data=ext.account_detail (obs = 10);
    where manufacturer = 'FORD' or product_segment = 'CHRYSLER' 
        and manufacturer_date between &start2 and &end2;
run;

The result is the following error: NOTE: Line generated by the macro variable "START2".

26          01JAN2017
              _______
              22
              76
ERROR: Syntax error while parsing WHERE clause.
ERROR 22-322: Syntax error, expecting one of the following: !!, *, **, +, -, /, AND, ||.  
ERROR 76-322: Syntax error, statement will be ignored.

Answer 1

You put the string 01JAN2017 into the macro variable START2 and then tried to us it in a WHERE statement without first converting it to an actual date value.

If you want your macro variables to be formatted in that way then use date literal syntax in your WHERE statement.

where manufacturer = 'FORD' or product_segment = 'CHRYSLER' 
  and manufacturer_date between "&start2"D and "&end2"D
;

Answer 2

This goes in another direction entirely, but the intnx function is well suited for this task. It allows you to increment time by intervals of your choice. The syntax is intnx('interval', fromdate, num_periods, 'alignment').

A few examples:

intnx('month', '20FEB17'd, 0, 'beg') will return 01FEB2017

intnx('month', '20FEB17'd, 2, 'end') will return 30APR2017

intnx('month', '20FEB17'd, -1, 'beg') will return 01JAN2017

In the first case, SAS read 20FEB17 as input, shifted it zero months, and returns the beginning of that period. In the second example, it shifts our input date forward two months, and returns the end of that period. In the final example, we see that we are able to shift backwards in time, going one month prior to our input date.

In your particular case, you would write something like... manufacturer_date between '01JAN17'd and intnx('month', '01JAN17'd, 5, 'end').

Note that somewhat counter-intuitively, we only need to shift forward FIVE months to capture a six-month window. This is because the 'end' alignment gives us the final month. Put differently, JAN -> FEB -> MAR -> APR -> MAY -> JUN requires 5 steps. Also note that I've only given examples with monthly shifts. SAS also allows for yearly shifts, semi-year shifts, and you can even program a custom interval if necessary.