DATA Step, Macro, Functions and more

TROUBLE WITH MACRO VARIABLES

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 122
Accepted Solution

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.

Any ideas? Thanks in advance.

data test;

  set farm_final;

  end_date=%eval(&sysdate-180);

  where orig_date between &sysdate and end_date;

run;

Attachment

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

Re: TROUBLE WITH MACRO VARIABLES

Posted in reply to maroulator

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;

View solution in original post


All Replies
Frequent Contributor
Posts: 122

Re: TROUBLE WITH MACRO VARIABLES

Posted in reply to maroulator

I should specify; orig_date is a character variable.

Super Contributor
Posts: 297

Re: TROUBLE WITH MACRO VARIABLES

Posted in reply to maroulator

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: 297

Re: TROUBLE WITH MACRO VARIABLES

Posted in reply to 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;

Frequent Contributor
Posts: 122

Re: TROUBLE WITH MACRO VARIABLES

Posted in reply to Scott_Mitchell

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;

Trusted Advisor
Posts: 1,228

Re: TROUBLE WITH MACRO VARIABLES

Posted in reply to maroulator

This is because orgin_date  is a character variable.

Super User
Super User
Posts: 7,955

Re: TROUBLE WITH MACRO VARIABLES

Posted in reply to maroulator

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

Posted in reply to maroulator


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,254

Re: TROUBLE WITH MACRO VARIABLES

Posted in reply to maroulator

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;

Super User
Posts: 10,028

Re: TROUBLE WITH MACRO VARIABLES

Posted in reply to maroulator

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 337 views
  • 6 likes
  • 7 in conversation