TROUBLE WITH MACRO VARIABLES

[maroulator]

I need the following code to run on the sample dataset attached. The goal is to select all values of orig_date between today and six months ago.

Any ideas? Thanks in advance.

data test;

  set farm_final;

  end_date=%eval(&sysdate-180);

  where orig_date between &sysdate and end_date;

run;

[maroulator]

I should specify; orig_date is a character variable.

[Scott_Mitchell]

orig_date appears as MM\YYYY.

When converting this to a date do you want the first day of the month or the last day of the month?

[Scott_Mitchell]

How about this?

DATA HAVE;

INFILE DATALINES;

INPUT ORIG_DATE $;

DATALINES;

01/2013

02/2014

04/2012

06/2014

09/2009

12/2008

03/2014

;

RUN;

%LET SIXMTH = %SYSFUNC(PUTN(%SYSFUNC(INTNX(MONTH,%SYSFUNC(DATE()), -6, SAME)), DATE9.));

DATA WANT (WHERE = (ORIGDATE BETWEEN DATE() AND "&SIXMTH"D));

SET HAVE;

ORGMTH = INPUT(SUBSTR(ORIG_DATE,1,INDEX(ORIG_DATE,'/')-1),BEST2.);

ORGYR  = INPUT(SUBSTR(ORIG_DATE,INDEX(ORIG_DATE,'/')+1),BEST4.);

ORIGDATE = MDY(ORGMTH,1,ORGYR);

FORMAT ORIGDATE DATE9.;

RUN;

[maroulator]

Thanks to both. When I run the following, i get the error "ERROR: Where clause operator requires compatible variables." Why is that and what can I do? The same occurs when I run the other code listed.

%let today_back_six = %sysfunc(putn(%sysfunc(intnx(MONTH, "&sysdate"d, -6, SAME)), date9.));

%put &today_back_six;

where orig_date between "&sysdate"d and "&today_back_six"d;

[stat_sas]

This is because orgin_date  is a character variable.

[RW9]

Well, without all the macro code:

data have;
  attrib orig_date format=$7.;
  input orig_date $;
datalines;
01/2013
02/2014
04/2012
06/2014
09/2009
12/2008
03/2014
;
run;

data want;
  set have;
  if intnx('month',today(),-6,'SAME') <=
        input("01"||substr(orig_date,1,2)||substr(orig_date,4,4),ddmmyy8.) <=
            today() then output;
run;

[slchen]

data want;
  set have;
  where intnx('day',input(orig_date,anydtdte7.),0,'b') between "&sysdate"d and "&today_back_six"d;
  run;

[SASKiwi · Accepted solution]

How about something like this?

%let today_back_six = %sysfunc(putn(%sysfunc(intnx(MONTH, "&sysdate"d, -6, SAME)), date9.));

%put &today_back_six;

where orig_date between "&sysdate"d and "&today_back_six"d;

[Ksharp]

end_date=%sysevalf( "&sysdate"d -180);