Create columns in a macro step

[mtrg92]

Hello,

I'm trying to do the following task;

we have a dataset of 100 columns  named col1 - col 100, all of them with different values.

in a new dataset, I want 300 columns : first 100 columns are from the previous dataset, then I add 100 columns that are named col101-col200 in such a way that col125 (for example) will be equal to col25. Same thing for the 100 other columns with col225 being equal to col25 of the original dataset. 

I thought that a macro will be helpful so I created the following one :

%macro test;
%do i=1 %to 100;
&j=i+100;
data b;set a;
col&j=col&i;
run;
%end;
%mend;
%test;

%macro test;
%do i=1 %to 100;
&j=i+200;
data b;set b;
col&j=col&i;
run;
%end;
%mend;
%test:

Unfortunately, the code above doesn't work at all, but I would like to know if someone has a suggestion of something that could lead me to my goal.

Thank you very much

[Tom · Accepted solution]

Turn on MPRINT option so you can see the code that is generated.

The first one is are doing:

&j=i+100;
data b;set a;
col101=col1;
run;
data b;set a;
col102=col2;
run;
...
data b;set a;
col201=col101;
run;

So the end result is just the last step.

Your second one is better. Since now you are reading and writing B.  So they will have an effect.  If the source of the data is actually A then add a step before the macro call to copy A to B.

data b ; set a; run;
%test;

But why not just move the %DO loop so you do it all in one data step?

%macro test;
data b;set a;
%do i=1 %to 100;
&j=i+100;
col&j=col&i;
%end;
run;
%mend;

Or skip the macro and just use normal old SAS code instead.

data b;
  set a;
  array in col1-col100;
  array out col101-col200;
  do over in;
      out=in;
  end;
run;

[Reeza]

This is better suited to an array rather than a macro.

*fake data;

data random;
    array _c(100) c1-c100;

    do nrows=1 to 50;

        do i=1 to dim(_c);
            _c(i)=rand('bernoulli', 0.8);
        end;
        output;
    end;
run;

data want;
    set random;
    array _c(10) c1-c10;
    array _c_1(101:200) c101-c200;
    array _c_2(201:300) c201 - c300;

    do i=1 to dim(_c);
        _c_1(i+100)=_c(i);
        _c_2(i+200)=_c(i);
    end;
run;

If you really do want a macro, you need to create the macro variable J using %LET and use %Eval for the calculation.

data b;
    set random;
    rename c1-c100 = col1-col100;
run;

%macro test;
    %do i=1 %to 100;
        %let j=%eval(&i+100);

        data b;
            set b;
            col&j=col&i;
        run;

    %end;
%mend;

%test;