Macro String Exclusions

[david27]

Team,

I have this:

%let total_period=20;

%let period1=201303;
%let period2=201306;
%let period3=201309;
%let period4=201312;
%let period5=201403;
%let period6=201406;
%let period7=201409;
%let period8=201412;
%let period9=201503;
%let period10=201506;
%let period11=201509;
%let period12=201512;
%let period13=201603;
%let period14=201606;
%let period15=201609;
%let period16=201612;
%let period17=201703;
%let period18=201706;
%let period19=201709;
%let period20=201712;
    
    
    
    

    data have;
input field :$8. _201303 _201306 _201309 _201312 _201403 _201406 _201409 _201412 _201503 _201506 _201509 _201512 _201603 _201606 _201609 _201612 _201703 _201706 _201709 _201712
;
datalines;
a 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
b 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
c 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
d 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
e 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
f 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
;
run;

I want to calculate mean as:

/*Excluding the period 201303 here*/
mean_ex_201303 =
mean(_201306, 
     _201309, _201312, 
     _201403, _201406, _201409, _201412, 
     _201503, _201506, _201509, _201512,  
     _201603, _201606, _201609, _201612, 
     _201703, _201706, _201709, _201712, );

/*Excluding the period 201306 here*/
mean_ex_201306 = mean(_201303, _201309, _201312, _201403, _201406, _201409, _201412, _201503, _201506, _201509, _201512, _201603, _201606, _201609, _201612, _201703, _201706, _201709, _201712, );

And this has to be done for all the periods i.e.201303 to 201712(all 20 months).

Please advise.

Thanks

[mkeintz · Accepted solution]

The "trick" here is to use two arrays constructed from variable name lists, where the lists are macrovars.  You don't need all those let statements:

data have;
  input field :$8. _201303 _201306 _201309 _201312 
                   _201403 _201406 _201409 _201412 
                   _201503 _201506 _201509 _201512 
                   _201603 _201606 _201609 _201612 
                   _201703 _201706 _201709 _201712;
datalines;
a 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
b 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
c 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
d 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
e 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
f 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
run;

/* Make a dummy data set with just the vars of interest*/
data dummy;  
  set have;
  keep _: ;
  stop;
run;
/* Use the dictionary metadata to build 2 variables lists */
proc sql noprint;
  select name, cats('mean_ex',name) into 
     :var_list separated by ' ',
     :mean_list separated by ' '
    from dictionary.columns where memname='DUMMY' and libname='WORK'
    order by name;
quit;
%let divisor=%eval(&sqlobs-1);
%put &=mean_list;
%put &=var_list;
%put &=divisor;

/* Now make an array of values and a corresponding array for means */
data want ;
  set have;
  array values {*} &var_list;
  array means  {*} &mean_list;
  sumvalues = sum(of values{*});
  do i=1 to dim(values);
    means{i} = (sumvalues - values{i}) / &divisor; 
  end;
  drop sumvalues;
run;

This program assumes you never have missing values, so the divisor is a constant (19 in your example).  If you do encounter missing vars then don't bother generating the divisor macrovar, and modify the data want step to:

/* Now make an array of values and a corresponding array for means */
data want ;
  set have;
  array values {*} &var_list;
  array means  {*} &mean_list;
  sumvalues=sum(of values{*});
  nvalues=n(of values{*});
  do i=1 to dim(values);
    means{i} = (sumvalues - values{i}) / (nvalues - n(values{i}));
  end;
  drop sumvalues nvalues;
run;

[Amir]

Hi,

As @mkeintz has indicated, using dictionary tables is more dynamic than hard coding %let statements. Nonetheless, the following macro should generate the statements required with the setup you've shown, for use in a data step:

%macro mean_period;
   %do i = 1 %to &total_period;
      %let p_list =;

      /* start assignment of mean value */
      mean_ex_&&period&i = mean(

      %do j = 1 %to &total_period;
         %if &j ne &i %then
            %let p_list = &p_list _&&period&j;
      %end;

      /* add commas to the list */
      %let p_list = %sysfunc(tranwrd(&p_list,%str( ),%str(, )));

      /* finish mean value assignment */
      &p_list
      );
   %end;
%mend mean_period;

options mprint;
data want;
   set have
   %mean_period;
run;

Regards,

Amir.