Reorder variables and move a specific variable to end of dataset

105tt — Fri, 06 Aug 2021 22:18:11 GMT

Hi,

See sample data below. I would like to reorder the variables so that "blue" is always at the end as "COLOR4". Looking at the first line with blue as "COLOR1", I would need the new "COLOR1" to be "black", then "red" as "COLOR2" , "green" as "COLOR3" , and then "blue" as color4.

Variables other than "blue" should stay in the same order but just get redefined as COLORS1,2,and 3. "Blue" should always be last as COLOR4.

Is there a code that can do this??

Sample dataset:

COLOR1	COLOR2	COLOR3	COLOR4
blue	black	red	green
black	green	blue	red
red	black	blue	green
red	green	black	blue

Here is what I am trying to achieve:

COLOR1	COLOR2	COLOR3	COLOR4
black	red	green	blue
black	green	red	blue
red	black	green	blue
red	green	black	blue

Thanks in advance!

105tt

Re: Reorder variables and move a specific variable to end of dataset

Reeza — Fri, 06 Aug 2021 22:27:08 GMT

Map your colours to numbers that will give you the hierarchy desired and use a format to have the colours displayed with the names.

Re: Reorder variables and move a specific variable to end of dataset

ballardw — Fri, 06 Aug 2021 22:38:30 GMT

@105tt wrote:

Hi,

See sample data below. I would like to reorder the variables so that "blue" is always at the end as "COLOR4". Looking at the first line with blue as "COLOR1", I would need the new "COLOR1" to be "black", then "red" as "COLOR2" , "green" as "COLOR3" , and then "blue" as color4.

Variables other than "blue" should stay in the same order but just get redefined as COLORS1,2,and 3. "Blue" should always be last as COLOR4.

Is there a code that can do this??

Sample dataset:

COLOR1 COLOR2 COLOR3 COLOR4

blue black red green

black green blue red

red black blue green

red green black blue

Here is what I am trying to achieve:

COLOR1 COLOR2 COLOR3 COLOR4

black red green blue

black green red blue

red black green blue

red green black blue

Thanks in advance!

105tt

What you request is a variation on very old code called a "Bubble sort". Which compares two values and then assigns an order.

Example;

data have;
   input COLOR1 $	COLOR2 $	COLOR3 $	COLOR4 $;
datalines;
blue	black	red	green
black	green	blue	red
red	black	blue	green
red	green	black	blue
;

data want;
   set have;
   array c (*) color1-color4;
   length temp $ 8.;
   do i=1 to (dim(c)-1);
      if c[i]='blue' then do;
         temp=c[i];
         c[i]=c[i+1];
         c[i+1]=temp;
      end;
   end;
   drop i temp;
run;

Please note the data step to create example data. It helps us provide better code when you provide data as working data step because it clarifies any issues about whether variables are numeric or character and such.

The Array holds the values of interest.

The Do loops over the first 3 values of the array comparing them with the next (higher index number value). So compares Color1 to Color2. If the current value of the "first" value is blue then using a temp variable to hold the value it switches places between 1 and 2. Then compares Color2 to Color3 and switches if needed.

Since you are only looking at a single specific value this is not the most efficient code but is possibly easier to modify if you need to do two specific values, like blue as last and green as next to last, as a later exercise.

Some programming languages have a Swap function that doesn't require the temporary variable that I use above in SAS.

When I say "old", I mean that I learned this in 1978.

topic Re: Reorder variables and move a specific variable to end of dataset in SAS Programming

Reorder variables and move a specific variable to end of dataset

Re: Reorder variables and move a specific variable to end of dataset

Re: Reorder variables and move a specific variable to end of dataset