topic Re: Help with Macro Variables in Do-Loops in SAS Programming

Help with Macro Variables in Do-Loops

HoaTruong — Fri, 07 Jul 2017 11:10:03 GMT

Hi,

I'm looking for assistance on what should hopefully be a simple question 🙂

I'm trying to build the following table, using the macro variables defined below, and the DO-loop. I'm having problems with the Target variable, as this is taking the number suffix from the loop counter...

i	CellID	Target
1	Work.Out1	Work.Out1
2	Work.Out1	Work.Out2
3	Work.Out1	Work.Out3
4	Work.Out1	Work.Out4

%LET NUMROWS=4;

%LET OUTTABLE1=Work.Out1;

%LET OUTTABLE2=Work.Out2;

%LET OUTTABLE3=Work.Out3;

%LET OUTTABLE4=Work.Out4;

Data Cell_Splits;

DO i=1 TO &NUMROWS;

CellID = "&OUTTABLE1";

Target = "&OUTTABLE&i";

Output;

END;

Run;

Any help would be greatly appreciated!

Many thanks

Re: Help with Macro Variables in Do-Loops

RW9 — Fri, 07 Jul 2017 11:19:14 GMT

Sorry, why are you using macro variables for this? Its a simple data task:

data want (drop=i);
  do i=1 to 4;
    cellid=cats("work.out",put(i,1.));
    target=cellid;
  end;
run;

Re: Help with Macro Variables in Do-Loops

WarrenKuhfeld — Fri, 07 Jul 2017 11:19:54 GMT

My advice is skip the macros and use a DATA step. Always remember that the macro substituions occur before the data step runs. &I (if it actually existed) would have one value. The data step variable i does exist and it has 4 values: 1 to 4.

Data Cell_Splits;
DO i=1 TO 4;
CellID = 'Work.Out1';
Target = cats('Woork.Data',i);
Output;
END;
Run;
proc print; run;

Re: Help with Macro Variables in Do-Loops

Kurt_Bremser — Fri, 07 Jul 2017 11:22:15 GMT

Data Cell_Splits;
DO i=1 TO &NUMROWS;
CellID = "&OUTTABLE1";
Target = "&OUTTABLE&i";
Output;
END;
Run;

I see no place in your code where macro variable i is defined.

Data step variables cannot be used as macro variables; the macro preprocessor does its work before the data step is compiled and long before the data step runs.

Since you literally define all the macro variables anyway, why don't you use a simple datastep with datalines?

data cell_splits;
length cellid target $20;
input cellid target;
datalines;
Work.Out1 Work.Out1
Work.Out1 Work.Out2
Work.Out1 Work.Out3
Work.Out1 Work.Out4
;
run;

or even simpler

data cell_splits;
retain cellid = "Work.Out1";
input target :$20.;
datalines;
Work.Out1
Work.Out2
Work.Out3
Work.Out4
;
run;

Re: Help with Macro Variables in Do-Loops

HoaTruong — Fri, 07 Jul 2017 11:46:14 GMT

Thanks for your reply. When this code is implemented, it won't know what values to use in the table beforehand. Even though, I've assigned simple values to the macro variables, the code will need to deal with any values that will passed to it.

Re: Help with Macro Variables in Do-Loops

Kurt_Bremser — Fri, 07 Jul 2017 11:50:13 GMT

When you have lists of values, it is best to keep them in datasets and work off those.

To know how to implement this principle, we'd have to see your code.

Re: Help with Macro Variables in Do-Loops

gamotte — Fri, 07 Jul 2017 12:12:03 GMT

Hello,

The "i" you use for looping is a column of your datase not a macrovariable so you

can not refer to it with "&i".

You can use a macro loop such as

%do i=1 %to &NUMROWS.;

...

%end;

and refer to your indexed macrovariables with :

&&OUTTABLE&i.

Note the "&&" as &OUTABLE&i. would separately try to resolve &OUTABLE and &i. (and fail).

Re: Help with Macro Variables in Do-Loops

Tom — Sat, 08 Jul 2017 20:03:47 GMT

So you have a series of macro variables and you want to create a dataset.

You can use the SYMGET() function to retreive the value of a macro variable by its name.

data want ;
  length i 8 CellID Target $41 ;
  do i=1 to &numrows ;
    cellid=symget(cats('outtable',i));
    target=cellid;
    output;
  end;
run;

Although it probably would have been better to make the dataset directly instead of first creating the series of macro variables.