topic Re: TROUBLE WITH MACRO VARIABLES in SAS Programming

TROUBLE WITH MACRO VARIABLES

maroulator — Mon, 14 Jul 2014 02:58:16 GMT

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;

Re: TROUBLE WITH MACRO VARIABLES

maroulator — Mon, 14 Jul 2014 03:08:52 GMT

I should specify; orig_date is a character variable.

Re: TROUBLE WITH MACRO VARIABLES

SASKiwi — Mon, 14 Jul 2014 03:25:57 GMT

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;

Re: TROUBLE WITH MACRO VARIABLES

Scott_Mitchell — Mon, 14 Jul 2014 03:53:55 GMT

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?

Re: TROUBLE WITH MACRO VARIABLES

Scott_Mitchell — Mon, 14 Jul 2014 04:10:04 GMT

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;

Re: TROUBLE WITH MACRO VARIABLES

maroulator — Mon, 14 Jul 2014 15:01:43 GMT

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;

Re: TROUBLE WITH MACRO VARIABLES

stat_sas — Mon, 14 Jul 2014 15:16:03 GMT

This is because orgin_date is a character variable.

Re: TROUBLE WITH MACRO VARIABLES

RW9 — Mon, 14 Jul 2014 15:18:41 GMT

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;

Re: TROUBLE WITH MACRO VARIABLES

slchen — Mon, 14 Jul 2014 15:37:35 GMT

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

Re: TROUBLE WITH MACRO VARIABLES

Ksharp — Tue, 15 Jul 2014 14:15:55 GMT

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