BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Mick_bill
Fluorite | Level 6

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,

 

1 ACCEPTED SOLUTION

Accepted Solutions
gamotte
Rhodochrosite | Level 12

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;

 

View solution in original post

5 REPLIES 5
KachiM
Rhodochrosite | Level 12
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.

Mick_bill
Fluorite | Level 6

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,

 



RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

 

gamotte
Rhodochrosite | Level 12

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;

 

Mick_bill
Fluorite | Level 6

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

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 1846 views
  • 1 like
  • 4 in conversation