BookmarkSubscribeRSS Feed
robby_beum
Quartz | Level 8

Assuming that the edateset variable is a SAS date (18871), I'm looping through 6 times and my desired result is decrease by one month so:

e1monthago should = 18840

e2monthago should = 18809

e3monthago should = 18778

etc...

Instead, I'm getting e1monthago, e2monthago, e3monthago, etc...

How can I resolve e&x.monthago to be a SAS Date instead of "e1monthago"? 

%macro exclude(count);
     %local x;
     %do x=1 %to &count;
          e&x.monthago = intnx('month',edateset,-&x.);
     %end;

%mend exclude;

%exclude(6);

Works but produces we following error and need help resolving it.

15933      %macro exclude(count);
15934         %local x;
15935         %do x=1 %to &count;
15936            e&x.monthago = intnx('month',edateset,-&x.);
15937         %end;
15938      %mend exclude;
15939
15940      %exclude(6);
MLOGIC(EXCLUDE):  Beginning execution.
MLOGIC(EXCLUDE):  Parameter COUNT has value 6
MLOGIC(EXCLUDE):  %LOCAL  X
SYMBOLGEN:  Macro variable COUNT resolves to 6
MLOGIC(EXCLUDE):  %DO loop beginning; index variable X; start value is 1; stop
      value is 6; by value is 1.
SYMBOLGEN:  Macro variable X resolves to 1
NOTE: Line generated by the macro variable "X".
1            e1monthago
             ----------
             180
ERROR 180-322: Statement is not valid or it is used out of proper order.

SYMBOLGEN:  Macro variable X resolves to 1
MPRINT(EXCLUDE):   e1monthago = intnx('month',edateset,-1);

MLOGIC(EXCLUDE):  %DO loop index variable X is now 2; loop will iterate again.
SYMBOLGEN:  Macro variable X resolves to 2
SYMBOLGEN:  Macro variable X resolves to 2
MPRINT(EXCLUDE):   e2monthago = intnx('month',edateset,-2);

MLOGIC(EXCLUDE):  %DO loop index variable X is now 3; loop will iterate again.
SYMBOLGEN:  Macro variable X resolves to 3
NOTE: Line generated by the macro variable "X".
1                 e2monthago
                  ----------
                  180
SYMBOLGEN:  Macro variable X resolves to 3
MPRINT(EXCLUDE):   e3monthago = intnx('month',edateset,-3);

ERROR 180-322: Statement is not valid or it is used out of proper order.

MLOGIC(EXCLUDE):  %DO loop index variable X is now 4; loop will iterate again.
SYMBOLGEN:  Macro variable X resolves to 4
NOTE: Line generated by the macro variable "X".
1                 e3monthago
                  ----------
                  180
SYMBOLGEN:  Macro variable X resolves to 4
MPRINT(EXCLUDE):   e4monthago = intnx('month',edateset,-4);

ERROR 180-322: Statement is not valid or it is used out of proper order.

MLOGIC(EXCLUDE):  %DO loop index variable X is now 5; loop will iterate again.
SYMBOLGEN:  Macro variable X resolves to 5
NOTE: Line generated by the macro variable "X".
1                 e4monthago
                  ----------
                  180
SYMBOLGEN:  Macro variable X resolves to 5
MPRINT(EXCLUDE):   e5monthago = intnx('month',edateset,-5);

ERROR 180-322: Statement is not valid or it is used out of proper order.

MLOGIC(EXCLUDE):  %DO loop index variable X is now 6; loop will iterate again.
SYMBOLGEN:  Macro variable X resolves to 6
NOTE: Line generated by the macro variable "X".
1                 e5monthago
                  ----------
                  180
SYMBOLGEN:  Macro variable X resolves to 6
MPRINT(EXCLUDE):   e6monthago = intnx('month',edateset,-6);

ERROR 180-322: Statement is not valid or it is used out of proper order.

MLOGIC(EXCLUDE):  %DO loop index variable X is now 7; loop will not iterate
      again.
MLOGIC(EXCLUDE):  Ending execution.
NOTE: Line generated by the macro variable "X".
1                 e6monthago
                  ----------
                  180

ERROR 180-322: Statement is not valid or it is used out of proper order.

Thanks,

Robby

14 REPLIES 14
art297
Opal | Level 21

You appear to be trying to create a dataset variable, but not within a datastep.  Where do you expect those six variables to reside?

robby_beum
Quartz | Level 8

Good Morning Art,

I've cut down the code...the next statement would be something like "call symput('e3monthago',e3monthago);" to make it a macro variable to be called any where in the program.  

I think the %let statement takes care of the error  "%let e&x.monthago = intnx('month',edateset,-&x.);"

Robby

   

art297
Opal | Level 21

Yes, a %let statement would work.  Call symput would give you an error as, again, it isn't wrapped within a datastep.  You may have to declare the macro variables as global if you need to access them throughout your program.

Peter_C
Rhodochrosite | Level 12

Try

   %put %exclude(6);

robby_beum
Quartz | Level 8

I guess I should piont out that my desired needed result is to resolve "e&x.monthago" to have a SAS Date 18840, 18809, 18879, etc... and not e1monthago, e2monthago, e3monthago, etc...if that makes sense.

Unfortunately, I've only been able to get the "e1monthago, e2monthago, e3monthago, etc..." and not the "18840, 18809, 18879, etc..."

Robby

Tom
Super User Tom
Super User

I am confused.  Let me try to re-state the task.

You have a MACRO variable EDATESET that contains a SAS date value.

You want to generate COUNT new MACRO variables that contain SAS date values for the previous COUNT months.

Anyway try:

%macro exclude(count,sdate);

%local i var;

%do i=1 %to &count ;

   %let var=e&i.monthago;

   %global &var;

   %let &var=%sysfunc(intnx(month,&sdate,-&i)) ;

%end;

%mend exclude;

Why would you call such a macro EXCLUDE?

Perhaps if you explain the larger task there is a much easier way to do what you want?



FriedEgg
SAS Employee

%macro exclude(count);

%local x;

%do x=1 %to &count;

  e&x.monthago=intnx('month',edateset,-&x);

%end;

%mend;

%macro mexclude(count);

%local x;

%do x=1 %to &count;

  %let e&x.monthago=%sysfunc(intnx(month,"&edateset"d,-&x),date9.);

  %put &&e&x.monthago;

%end;

%mend;

data want;

input edateset yymmdd8.;

call symput('edateset',put(edateset,date9.));

%exclude(6)

format e: date9.;

put _all_;

cards;

110930

;

run;

%mexclude(6)

Tom
Super User Tom
Super User

Is the problem how to resolve a macro variable name when you want to use another macro variable's value as part of the name?

If so then use extra &'s.

Try this little bit of code:

%let e1monthago='01AUG2011'd;

%let N=1;

%put e&n.monthago=&&e&n.monthago;

BobD
Fluorite | Level 6

I'm sorry, but are you trying to create six macro variables or six data step variables?  And where does edateset come from?  Is it a variable in some data set?

If edateset comes from a SAS data set and you want macro variables, change your macro to use call symput (not %let):

%macro Mexclude(count);

     %local x;

     %do x=1 %to &count;

          call symput("e&x.monthago",intnx('month',edateset,-&x.));

     %end;

%mend Mexclude;

Note you must run this inside a data step.  Your original macro will create six SAS variables; the "new" macro will create six macro variables.

data want;

   set have;

   %exclude(6);

   %Mexclude(6);

run;

Also note that the macros will run for each observation in your data set "have"; new SAS variables will be created for each observation, but only the six macro variables will exist at the end.

art297
Opal | Level 21

Robby,

Since everyone appears to be equally confused I have to ask: are you ONLY trying to accomplish something like the following?:

data have;

  format edateset date9.;

  input edateset date9.;

  cards;

9JAN2005

15APR2010

;

data want (drop=i);

  set have;

  array monthsago(6);

  format monthsago: date9.;

  do i=1 to 6;

    monthsago(i)=intnx('month',edateset,-i);

  end;

run;

robby_beum
Quartz | Level 8

Art,

Basically yes - but I need them in local (or global) macro variables (instead of a table) so I can plug them into SQL where statements many places down the line.

I was looking for:

macro variable named &e1monthago that resolves to sas date 18840.

macro variable named &e2monthago that resolves to sas date 18809.

macro variable named &e3monthago that resolves to sas date 18778.

I can easily make the following but was trying to do in a loop

%LET  edateset=18871;

data _null_;
   e1monthago = intnx('month',edateset,-1);
   call symput('e1monthago',e1monthago);
   e1dateago=put(e1monthago, mmddyy10.);
   call symput('e1dateago',e1dateago);

   e2monthago = intnx('month',edateset,-2);
   call symput('e2monthago',e2monthago);
   e2dateago=put(e2monthago, mmddyy10.);
   call symput('e2dateago',e2dateago);

   ...6 times...

run;

Thanks four your help - pretty much bagged it since the effort is more time than I can afford.

Robby

FriedEgg
SAS Employee

data _null_;

input edateset yymmdd8.;

call symput('edateset',put(edateset,date9.));

do i=1 to 6;

  call symput('e' || strip(i) || 'monthago',put(intnx('month',edateset,-i),date9.));

end;

cards;

110930

;

run;

%put &edateset &e1monthago &e2monthago &e3monthago &e4monthago &e5monthago &e6monthago;

30SEP2011 01AUG2011 01JUL2011 01JUN2011 01MAY2011 01APR2011 01MAR2011

BobD
Fluorite | Level 6

Well, if edateset is a macro variable, do this:

%let edateset=18871;
%macro exclude(count);
     %local x;
     %do x=1 %to &count;
        %global e&x.monthago;
        %let e&x.monthago = %sysfunc(intnx(month,&edateset,-&x.));
     %end;
%mend exclude;

%exclude(6);

%put e1monthago=&e1monthago;
%put e2monthago=&e2monthago;
%put e3monthago=&e3monthago;
%put e4monthago=&e4monthago;
%put e5monthago=&e5monthago;
%put e6monthago=&e6monthago;

Koos
Calcite | Level 5

Hi Robby,

Some usefull answers provided, here is something alse as well:

*You could use the actual date;

%let edateset=%sysfunc(putn("01Sep2011"d, 8.));

*Or the SAS data you provided;

%let edateset=18871;

%macro exclude(count);

     %local x;

     %do x=1 %to &count;

          %let e&x.monthago = %sysfunc(intnx(month,&edateset.,-&x.));

             *you need to use && to resolve the macro name after the numeric has been resolved.

             *the macro processor will first resolve all values with a single &, e.g. &x. and double &'s will resove to a single &,

             *thus the first resolution will give &e1monthago. This is the actual variable name that you are looking for finally;

             %put &&e&x.monthago;

     %end;

%mend exclude;

%exclude(6);

*COMPLETE SOLUTION;

*For the where clause you could use;

%macro exclude(count);

     %local x;

      *this will initialise the vairable and global it, but we want to clear it in case we run it more than once;

      %global whrein;

      *clear the variable;

      %let whrein=;

     %do x=1 %to &count.;

          %let e&x.monthago = %sysfunc(intnx(month,&edateset.,-&x.));

             *add the assigned variable to the string;

             %let whrein=&whrein &&e&x.monthago;

             *add a comma for the where clause if it is not the final value;

             %if %eval(&x. ne &count.) %then %let whrein=&whrein,;

     %end;

      *you can also add the baarckets here, or later in the where clause;

      %let whrein=(&whrein.);

      %put &whrein.;

%mend exclude;

%exclude(6);

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 14 replies
  • 6645 views
  • 6 likes
  • 7 in conversation