topic Re: Selecting accounts to reverse within an Array in SAS Programming

Selecting accounts to reverse within an Array

Mick_bill — Tue, 07 Mar 2017 10:43:19 GMT

Hello, I currently have the data set below and what I am trying to do is reverse the order of the numeric type from lowest to highest to highest to lowest whilst keeping the type in the same order.

E.g. it has to stay A - B - C but the number order switches from 1 - 2 - 3 to 3 - 2 - 1.

data ORIGINAL;

input TYPE_1 $ TYPE_2 $ TYPE_3 $ TYPE_4 $ TYPE_5 $ TYPE_6 $ NUM_1 NUM_2 NUM_3 NUM_4 NUM_5 NUM_6;

datalines;

A A A B B C 6 8 12 7 8 9

A A B B C C 2 10 1 11 7 10

A B B B B C 7 1 8 9 10 6

A A B C C C 1 3 4 1 2 3

;

RUN;

I have this code below which will switch it all but the issue with this is that it is also swicthing the char also where I do not need this to happen.

DATA REVERSE; SET ORIGINAL;

ARRAY CHAR [*] TYPE_1 TYPE_2 TYPE_3 TYPE_4 TYPE_5;

ARRAY NEW_CHAR [*] TYPE_5 TYPE_4 TYPE_3 TYPE_2 TYPE_1;

ARRAY NUM [*] NUM_1 - NUM_5;

ARRAY NEW_NUM [*] NUM_5 - NUM_1;

CALL SORTC(OF NEW_CHAR[*]);

CALL SORTN(OF NEW_NUM[*]);

RUN;

Does anybody know of any code which would allow me to swicth the numeric values around whilst keeping them in the correct character order?

Thank you,

Re: Selecting accounts to reverse within an Array

KachiM — Tue, 07 Mar 2017 10:32:04 GMT

DATA REVERSE; SET ORIGINAL;
ARRAY NUM [*] NUM_1 - NUM_5;
ARRAY NEW_NUM [*] NUM_5 - NUM_1;
CALL SORTN(OF NEW_NUM[*]);
RUN;

TRY THE ABOVE and see whether it works for you.

Re: Selecting accounts to reverse within an Array

Mick_bill — Tue, 07 Mar 2017 10:50:30 GMT

hello,

Thank you for this. The only issue is I am still trying to get the Type to Align with the Value,

So the solution should look like this:

data Final_outcome;

input TYPE_1 $ TYPE_2 $ TYPE_3 $ TYPE_4 $ TYPE_5 $ TYPE_6 $ NUM_1 NUM_2 NUM_3 NUM_4 NUM_5 NUM_6;

datalines;

A A A B B C 12 8 6 8 7 9

A A B B C C 10 2 11 1 10 7

A B B B B C 7 10 9 8 1 6

A A B C C C 3 1 4 3 2 1

;

RUN;

From this you can see that one the first line
Originally this was seen as:

A A A B B C 6 8 12 7 8 9

What I am looking for is this to now be:
A A A B B C 12 8 6 8 7 9

So where type 1 - 3 are A it has swapped the first three numerics to highest and lowest, from lowest to highest, not swapping all the outcomes. This is then done for the Char Type B swapping to highest - lowest and then the same for C if this had to be switch.

Currently each Character is aligned to the numeric so if I was to just change the numeric and not the character these would not longer match to the outcome.

Thank you,

Re: Selecting accounts to reverse within an Array

RW9 — Tue, 07 Mar 2017 10:59:48 GMT

Edit: And do get into the habbit of using good programming practices - keeping code lower case, all the same case, indentented etc. And use the {i} above your post when you want to post code to keep it separate from text.

Well, I assume the character and numerics are linked somehow. Its hard to tell from your description. If so then the below will show a way of sorting data any way you like. What it does is normalise the data (down rather than across, database/SAS rather than Excel), perform a sort, then transpose the data up again. It is simpler this way as normalised data is far easier to work with programmatically than transposed data. Also note that this code is not fixed to 6 of each, will do any number of the array.

data original;
  input type_1 $ type_2 $ type_3 $ type_4 $ type_5 $ type_6 $ num_1 num_2 num_3 num_4 num_5 num_6;
  id=_n_;
datalines;
A A A B B C 6 8 12 7 8 9
A A B B C C 2 10 1 11 7 10
A B B B B C 7 1 8 9 10 6
A A B C C C 1 3 4 1 2 3
;
run;

data inter (keep=id type num);
  set original;
  array t{*} type_:;
  array n{*} num_:;
  do i=1 to dim(t);
    type=t{i};
    num=n{i};
    output;
  end;
run;

proc sort data=inter;
  by id num type;
run;

data want (drop=type num curr);
  set original (obs=0) inter;
  by id;
  array t{*} type_:;
  array n{*} num_:;
  retain type_: num_: curr;
  if first.id then curr=1;
  t{curr}=type;
  n{curr}=num;
  curr=curr+1;
  if last.id then output;
run;

Re: Selecting accounts to reverse within an Array

gamotte — Tue, 07 Mar 2017 11:19:14 GMT

I had almost the same solution as @RW9. I post it anyway.

data a;
	set ORIGINAL;
	keep OBS TYPE VAL;
	array char (*) _CHARACTER_;
	array num (*) _NUMERIC_;
	do i=1 to dim(char);
		OBS=_N_;
		TYPE=char(i);
		VAL=num(i);
		output;
	end;
run;

proc sort data=a;
	by OBS TYPE DESCENDING VAL;
run;

data want;
    set a;
	format TYPE_1-TYPE_6 $1.;
	retain TYPE_1-TYPE_6 NUM_1-NUM_6;
	by OBS;
	array TYPES (*) TYPE_1-TYPE_6;
	array NUMS (*) NUM_1-NUM_6;
	do i=1 to dim(TYPES);
		if mod(_N_,6)=mod(i,6) then do;
			TYPES(i)=TYPE;
			NUMS(i)=VAL;
		end;
	end;

	if last.OBS;
	drop TYPE VAL OBS i;
run;

Re: Selecting accounts to reverse within an Array

Mick_bill — Tue, 07 Mar 2017 12:35:30 GMT

Thank @gamotte and @RW9they have both worked perfectly!!

A transpose was the way forward rather than trying to use a reversal within the array itself.

Thanks.,

Michael