topic Re: order observation based on the value provided in the column in SAS Programming

order observation based on the value provided in the column

zqkal — Tue, 14 May 2019 16:42:19 GMT

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;

Re: order observation based on the value provided in the column

PaigeMiller — Tue, 14 May 2019 16:48:45 GMT

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.

Re: order observation based on the value provided in the column

Reeza — Tue, 14 May 2019 16:53:52 GMT

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.

Re: order observation based on the value provided in the column

novinosrin — Tue, 14 May 2019 17:12:07 GMT

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.

Re: order observation based on the value provided in the column

Reeza — Tue, 14 May 2019 17:23:02 GMT

@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 🙂

Re: order observation based on the value provided in the column

novinosrin — Tue, 14 May 2019 17:24:38 GMT

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

Re: order observation based on the value provided in the column

novinosrin — Tue, 14 May 2019 18:38:39 GMT

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.

Re: order observation based on the value provided in the column

FreelanceReinh — Tue, 14 May 2019 19:58:59 GMT

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.