## TROUBLE WITH MACRO VARIABLES

Solved
Frequent Contributor
Posts: 126

# TROUBLE WITH MACRO VARIABLES

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.

data test;

set farm_final;

end_date=%eval(&sysdate-180);

where orig_date between &sysdate and end_date;

run;

Accepted Solutions
Solution
‎07-13-2014 11:25 PM
Super User
Posts: 3,927

## Re: TROUBLE WITH MACRO VARIABLES

%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;

All Replies
Frequent Contributor
Posts: 126

## Re: TROUBLE WITH MACRO VARIABLES

I should specify; orig_date is a character variable.

Super Contributor
Posts: 312

## Re: TROUBLE WITH MACRO VARIABLES

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?

Super Contributor
Posts: 312

## Re: TROUBLE WITH MACRO VARIABLES

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;

Frequent Contributor
Posts: 126

## Re: TROUBLE WITH MACRO VARIABLES

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;

Posts: 1,270

## Re: TROUBLE WITH MACRO VARIABLES

This is because orgin_date  is a character variable.

Super User
Posts: 9,599

## Re: TROUBLE WITH MACRO VARIABLES

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;

Super Contributor
Posts: 275

## Re: TROUBLE WITH MACRO VARIABLES

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

Solution
‎07-13-2014 11:25 PM
Super User
Posts: 3,927

## Re: TROUBLE WITH MACRO VARIABLES

%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;

Super User
Posts: 10,787