How to pass variable sequence (list) to SAS Macro

Question

I have variables named _200701, _200702,... till _201612, each containing specific numeric data for that month. From these, I want to substract specific amount (variable cap_inc), if a condition is met:

%MACRO DeleteExc(var);
DATA Working.Test;
SET Working.Test;
    IF &var. GE cap_inc THEN &var. = SUM(&var., - cap_inc);
    ELSE &var. = &var.;
RUN;
%MEND;

Code is working if I put only one month as a parameter (eg _200909)... But I want to put there sequence from these variables. I have tried combinations like "OF _200701 -- _201612" or "OF _20:", but nothing has worked.

I have also another macro, using parmbuff parameter, working in the "for each loop" way, where I can put more variables separated by comma, for instance

%DeleteExc(_200701, _200702, _200703)

But I still can't pass all variables in some convenient, easy to follow way. (I don't want to type all parameters as there is 120 of them).

Is there any way how to do this? Thank you!

Answer 1

First thing is that if you want to pass a list into a macro then DO NOT delimit the list using a comma. It will just make calling the macro a large pain. You will will either need to use macro quoting to hide the comma. Or override SAS's parameter processing by using the /parmbuff option and add logic to process the &syspbuff macro variable yourself. Use some other character that is not used in the values as the delimiter. Like | or ^ for example. For a list of variable names use spaces as the delimiter.

%DeleteExc(varlist=_200701 _200702 _200703)

Then you can use the macro variable anywhere SAS expects a list of variables.

array in &varlist ;
total = sum(of &varlist);

Now since your list is really a list of MONTHS then give your macro the start and end month and let it generate the list for you.

%macro DeleteExc(start,end);
  %local i var ;
  %do i=0 %to %sysfunc(intck(month,&start,&end)) ;
    %let var=_%sysfunc(intnx(month,&start,&i,b),yymmn6);
IF .Z < cap_inc < &var. THEN &var. = &var - cap_inc;
  %end;
%mend;
DATA Working.Test;
  SET Working.Test;
%DeleteExc("01JAN2007"d,"01DEC2016"d);
RUN;

Answer 2

Here are a few options - perhaps there's one you haven't tried?

data example;
    array months{*} _200701-_200712 _200801-_200812 (24*1);
    array underscores{*} _:;
    _randomvar = 100;
    s1 = sum(of _200701-_200812); /*Generates lots of notes about uninitialised variables but gives correct result*/
    s2 = sum(of _200701--_200812); /*Works only if there are no rogue columns in between month columns*/
    s3 = sum(of months{*}); /* Requires array definition*/
    s4 = sum(of _:); /*Sum any variables with _ prefix - potentially including undesired variables*/

    put (s1-s4)(=);
run;

Answer 3

The double dash (--) variable name range list can be used to specify the variables in an array. A simple iterative DO LOOP lets you perform the desired operation on each variable.

data want;
  set have;
  array month_named_variables _200701 -- _201612;
  do _index = 1 to dim(month_named_variables); drop _index;

    IF month_named_variables(_index) GE cap_inc THEN 
      month_named_variables(_index) = SUM(month_named_variables(_index), - cap_inc);
    ELSE
      month_named_variables(_index) = month_named_variables(_index);

  end;
run;

If the data set has extra variables within the name range you can still use an array and non-macro code:

data want;
  set have;
  array nums _numeric_;

  do _index = 1 to dim(nums); drop _index;

    _vname = vname(nums(_index)); drop _vname;
    if _vname ne: '_' 
      or not (2007 <= input(substr(_vname,2,4), ??4.) <= 2016)
      or not (01   <= input(substr(_vname,6,2), ??2.) <= 12)
      or not length(_vname) = 7 
    then continue;

    IF nums(_index) GE cap_inc THEN 
      nums(_index) = SUM(nums(_index), - cap_inc);
    ELSE
      nums(_index) = nums(_index);

  end;
run;

If you really need use a specific list of variables and want to work within a macro, I would recommend passing the FROM and TO values corresponding to the variable names and looping that range according to the naming convention:

%macro want(data=, yyyymm_from=, yyyymm_to=, guard=1000, debug=0);
  %local LOWER UPPER YEARMON INDEX NVARS;
  %let LOWER = %sysfunc(inputn(&yyyymm_from,yymmn6.));
  %let UPPER = %sysfunc(inputn(&yyyymm_to,yymmn6.));

  %let INDEX = 1;
  %do YEARMON = &LOWER %to &UPPER;
    %let yyyymm = %sysfunc(putn(&YEARMON, yymmn6.));

    %local ymvar&INDEX;
    %let ymvar&INDEX = _&yyyymm;  %* NAMING CONVENTION;

    %if &debug %then %put NOTE: YMVAR&INDEX=%superq(YMVAR&INDEX);

    %if &INDEX > &GUARD %then %do;
      %put ERROR: Exceeded guard limit of &GUARD variables;
      %return;
    %end;

    %let NVARS = &INDEX;

    %let YEARMON = %sysfunc(INTNX(MONTH,&yearmon,1));  %* NAMING CONVENTION;
    %let YEARMON = %eval(&YEARMON-1); %* back off by one for implicit macro do loop increment of +1;

    %let INDEX = %eval(&INDEX+1);
  %end;

  %do INDEX = 1 %to &NVARS;
    %put NOTE: &=INDEX YMVAR&INDEX=&&&YMVAR&INDEX;
  %end;
%mend;

%want (data=have,  yyyymm_from=200701, yyyymm_to=201612)

Answer 4

If my understanding is correct, you want to do loop with month,which is defendant on variables in data, you could set start date and end date, then do loop.

%macro month_loop(start,end);
%let start=%sysfunc(inputn(&start,yymmn6.));
%let end=%sysfunc(inputn(&end,yymmn6.));
%let date=&start;
%do %until (%sysfunc(indexw("&date","&end")));
     %let date=%sysfunc(intnx(month,&date,1));
     %let var=_%sysfunc(putn(&date,yymmn6.));
     data want;
        set have;
        IF &var. GE cap_inc THEN &var. = SUM(&var., - cap_inc);
        ELSE &var. = &var.;
     run;
%end;
%mend;
%month_loop(200701,201612)