order observation based on the value provided in the column

[zqkal]

Deal SAS Users, 

I would like to change the order of rows based on the values provided in the "order" column. for example. The order variable only contains the letter the first letter. however, the numbers will be sorted in descending order. 

For example: 

data have;
	input type $ desc $ mo order $10.;
	datalines;
A D2 0 B,D
A D1 1 B,D
A B1 2 B,D
B D1 0 D,B
B B0 1 D,B
B D2 2 D,B
B B1 3 D,B
C D2 0 B,D
C D1 1 B,D
C B1 2 B,D
D X2 0 Z,Y,X
D Y1 1 Z,Y,X
D Z1 2 Z,Y,X
;
run;

data want;
	input type $ desc $ mo order $ 7-12 new_desc $ 13 - 15;
	datalines;
A D2 2 B,D   B1
A D1 0 B,D   D2
A B1 1 B,D   D1
B D1 2 D,B   D2
B B0 0 D,B   D1
B D2 3 D,B   B1
B B1 1 D,B   B0
C D2 2 B,D   B1
C D1 0 B,D   D2
C B1 1 B,D   D1
D X2 2 Z,Y,X Z1
D Y1 1 Z,Y,X Y1
D Z1 0 Z,Y,X X2
;
run;

[PaigeMiller]

In your desired output data set, the rows are in the same order as they are in the input data set. So, I don't understand the question.

[Reeza]

It almost looks like you want to pull out the DESC column by TYPE and sort on that but that messes up the data because it's not aligned. So far this doesn't make sense to me. You could do that pretty easily and remerge it, just not sure how that could add any value to an analysis.

[novinosrin]

Hi @zqkal   The problem isn't hard and is rather simple with Hashes. Would you be comfortable maintaining it? Also,  is this Ad-hoc or for production ? 

I know I should have posted the solution and then left those comments, but I would like to know beforehand as I am lazy.

[Reeza]

@novinosrin it's better to ask now and then develop the solutions otherwise you waste your time developing solutions that may not be used. IMO asking for clarification first is always fine 🙂

[novinosrin]

@Reeza   Absolutely!  🙂 and can't agree more at lunch time

[novinosrin · Accepted solution]

Hello @zqkal 

data have;
	input type $ desc $ mo order $10.;
	datalines;
A D2 0 B,D
A D1 1 B,D
A B1 2 B,D
B D1 0 D,B
B B0 1 D,B
B D2 2 D,B
B B1 3 D,B
C D2 0 B,D
C D1 1 B,D
C B1 2 B,D
D X2 0 Z,Y,X
D Y1 1 Z,Y,X
D Z1 2 Z,Y,X
;
run;


data temp ;
if _n_=1 then do;
if 0 then set have;
   dcl hash H (dataset:'have(drop=order)',multidata:'y') ;
   h.definekey  ("type") ;
   h.definedata (all:'y') ;
   h.definedone () ;
   end;
  set have(keep=type order);
  by type;
  if first.type;
  do _i=1 to countw(order,',');
  _k=scan(order,_i,',');
  	do rc=h.find() by 0 while (rc=0);
	if first(desc)=_k then output;
	rc=h.find_next();
	end;
end;
rename desc=newdesc;
drop _: rc;
run;

data want;
if 0 then set have;
merge have(keep=type desc) temp;
by type;
run;

Of course, the TEMP step and the WANT step can quite comfortably be combined into one, but it easier to follow and maintain when as two passes. 

[FreelanceReinh]

Hi @zqkal,

Just in case that you "would like to change the order of rows based on the values provided in the 'order' column ...," i.e., not create a new variable (new_desc), but just sort the observations in a certain way, you can possibly use an ORDER BY clause in PROC SQL.

Example:

proc sql;
create table want as
select * from have
order by type, indexc(order, first(desc)), input(substr(desc,2),16.) desc;
quit;

This assumes that dataset HAVE is sorted by TYPE.