Macro to loop through variables and store results SAS

Question

I have the following variables: A_Bldg B_Bldg C_Bldg D_Bldg. I want to multiply them by INTSF and store the result in a new variable, Sale_i. For example, A_Bldg * INTSF = Sale_A, B_Bldg * INTSF = Sale_B, and so on.

My code is:

%macro loopit(mylist);
%let n=%sysfunc(countw(&mylist));
%do J = 1 %to &n;
%let i = %scan(&mylist,&J);

  data test;
  set data;
  sale_&i. = &i._Bldg * INTSF;
  run;

%end;
%mend;

%let list = A B C D;
%loopit(&list);

This only produces Sale_D, which is the last letter in the list. How do I get Sales A-C to appear? The first four lines of code are so I can loop through the text A-D. I thought about doing it with arrays, but didn't know how to choose the variables based on the A-D indicators. Thanks for your help!

score 2 · Accepted Answer · answered Jul 21 '16 at 19:18

You're currently looping through your list and recreating the test dataset every time, so it only appears to have sale_d because you're only viewing the last iteration.

You can clean up your loop by scanning through your list in one data step to solve your problem:

%let list = A B C D;

%macro loopit;

    data test;
        set data;
            %do i = 1 %to %sysfunc(countw(&list.));
            %let this_letter = %scan(&list., &i.);
                sale_&this_letter. = &this_letter._Bldg * INTSF;
            %end;
    run;

%mend loopit;

%loopit;

score 1 · Answer 2 · answered Jul 21 '16 at 19:27

Your %DO loop is in the wrong place. But really you do not need to use macro code to do something that the native SAS code can already do.

data want;
  set have ;
  array in A_Bldg B_Bldg C_Bldg D_Bldg ;
  array out sale_1-sale4 ;
  do i=1 to dim(in);
    out(i)=intsf*in(i);
  end;
run;

Macro to loop through variables and store results SAS

2 Answers2