SAS how to change variable(column) position(values also) using ARRAY

3
votes

I am new to SAS and meet a problem to switch column content.

I have a dataset like:

data switch;
input total A1 A2 A3 A4 A5 A6 A7 A8 A9 A10;
cards;
1234567890 1 2 3 4 5 6 7 8 9 0
1234567890 1 2 3 4 5 6 7 8 9 0
1234567890 1 2 3 4 5 6 7 8 9 0
1234567890 1 2 3 4 5 6 7 8 9 0
1234567890 1 2 3 4 5 6 7 8 9 0
1234567890 1 2 3 4 5 6 7 8 9 0
1234567890 1 2 3 4 5 6 7 8 9 0
1234567890 1 2 3 4 5 6 7 8 9 0
1234567890 1 2 3 4 5 6 7 8 9 0
;
run;

So the goal is to get the values in A10 and fill into the A1, and A9 to A2, A8 to A3...etc. LIKE:

total      A1 A2 A3 ...
1234567890 0  9  8
1234567890 0  9  8
1234567890 0  9  8

So generally to switch the columns(except first variable) backward.

And Array is required.

I dont know how to do it at all, because SAS reads rows(observations).

Can someone walk me through this?

Many thanks!

============================= Thanks for your thoughts everyone, I also figured out a way, which is also borrowing a temp or middle array:

data want;
set switch;
array old(1:10) A1 - A10;
array mid(1:10) B1 - B10;

do i=1 to 10;
mid[i]=old[i];
end;

array new(1:10) A1 - A10;
do i=1 to 10;
new[i]=mid[11-i];   /* input in new array with mid array in reverse order */
end;

drop B1 - B10 i;  /* drop unwanted column */
run;
4
arraysvariablessas

4 Answers

2
votes

Here is a way using two arrays and two do loops. Essentially storing values in new variables in reverse order, then moving them back to the original variables. A similar kind of method could be done without an array using the rename statement.

data want;
  set switch;

  array var (*) A1-A10;
  array holder (10) H1-H10;

  do i = 1 to (10);
    holder{i} = var{(10 - i + 1)};
  end;

  do i = 1 to 10;
    var{i} = holder{i};
  end;

  drop H1-H10 i;  
run;

Edit: Reduced method down to just one temporary variable, one array and one do loop:

data want;
  set switch;
  array var (*) a1-a10;


  do i = 1 to (floor(dim(var)/2));

    a11 = var{i};
    var{i}  = var{(dim(var)- i +1)};
    var{(dim(var) - i +1)} = a11;

  end;

  drop i a11;
run;
0
votes

Multiply everything by -1, use call sortn, then multiply by -1 again.

Unless you have more varied data...?

Otherwise, transpose, sort and transpose again.

0
votes

Here's a solution that reverses the order or values and does not depend on sorting. There are 3 arrays created, one for the original order of variables, one for the variables in reverse order and a temporary array that is populated with the same values as the reversed array. This extra array is necessary as the values in it won't get updated during the do loop, which would be a problem if you just used the first 2 arrays.

data switch;
input total A1 A2 A3 A4 A5 A6 A7 A8 A9 A10;
cards;
1234567890 1 2 3 4 5 6 7 8 9 0
1234567890 1 2 3 4 5 6 7 8 9 0
1234567890 1 2 3 4 5 6 7 8 9 0
1234567890 1 2 3 4 5 6 7 8 9 0
1234567890 1 2 3 4 5 6 7 8 9 0
1234567890 1 2 3 4 5 6 7 8 9 0
1234567890 1 2 3 4 5 6 7 8 9 0
1234567890 1 2 3 4 5 6 7 8 9 0
1234567890 1 2 3 4 5 6 7 8 9 0
;
run;

data want;
set switch;
array vars(*) A1-A10; /* array in original order */
array rev_vars(*) A10-A1; /* array in reverse order */
array _temp(10) _temporary_;
/* populate temporary array with values in reverse order */
do i=1 to dim(rev_vars);
    _temp{i} = rev_vars{i};
end;
/* rewrite existing values in reverse order */
do i=1 to dim(vars);
    vars{i}=_temp{i};
end;
drop i;
run;
0
votes

Here's yet another solution that only requires three temporary variables, instead of temporary arrays - it swaps pairs of values in the array, working from the ends to the middle. It will still work correctly if the array has an odd number of elements, leaving the middle element unchanged.

data switch;
input total A1 A2 A3 A4 A5 A6 A7 A8 A9 A10;
cards;
1234567890 1 2 3 4 5 6 7 8 9 0
1234567890 1 2 3 4 5 6 7 8 9 0
1234567890 1 2 3 4 5 6 7 8 9 0
1234567890 1 2 3 4 5 6 7 8 9 0
1234567890 1 2 3 4 5 6 7 8 9 0
1234567890 1 2 3 4 5 6 7 8 9 0
1234567890 1 2 3 4 5 6 7 8 9 0
1234567890 1 2 3 4 5 6 7 8 9 0
1234567890 1 2 3 4 5 6 7 8 9 0
;
run;

data out;
  set switch;
  array a[*] a1-a10;
  _a = 1;
  _b = dim(a);
  do while (_a < _b);
    * Swap elements _A and _B;
    _temp = a[_a];
    a[_a] = a[_b];
    a[_b] = _temp;
    * _A moves forward from the beginning of the array;
    _a = _a + 1;
    * _B moves backward from the end;
    _b = _b - 1;
  end;
run;