Sorting and grouping across more columns with missing data

[PavelD]

hi!

I am struggling with a seemingly simple problem.

Have: see below

Want: grouped by entity, these groups then sorted by per. So the highest per at the top. Per is missing when period=2.

I consulted "Custom Sorting with the Case Statement" but didn't manage to connect the dots.

Any ideas? Thank you

have	entity	period	per	
	A	1	5	
	B	1	9	
	C	1	7	
	A	2	.	
	C	2	.	
	B	2	.	
				
want	entity	period	per	<< grouped by entity, sorted by per
	B	1	9	
	B	2	.	
	C	1	7	
	C	2	.	
	A	1	5	
	A	2	.	

edit 2018-12-10 16:26: clarification (entity order in "want" is not important, but they need to be grouped)

edit 2018-12-11 08:13: following @Reeza's suggestion, below is my tentative (but working) implementation:

edit 2018-12-11 14:13: you may shorten the code by using the SAS rank procedure instead of data step / proc sort combination.

data have;
  input entity $ period per;
  datalines;
A 1 5 
B 1 9 
C 1 7 
A 2 . 
C 2 . 
B 2 .
;
run;
/* sorted have dataset
entity	period	per
B	1	9
C	1	7
A	1	5
A	2	.
C	2	.
B	2	.
*/
proc sort data = have; by descending per period; run;
/* have1 = have with a custom order added (period=1 only)
entity	period	per	custom_order
B	1	9	1
C	1	7	2
A	1	5	3
A	2	.	.
C	2	.	.
B	2	.	.
*/
data have1;
  set have;
  if period = 1 then custom_order + 1  ;
  else custom_order = .;
run;
/* have1, sorted 
entity	period	per	custom_order
A	1	5	3
A	2	.	.
B	1	9	1
B	2	.	.
C	1	7	2
C	2	.	.
*/
proc sort data = have1; by entity period; run;

/* want, unsorted yet 
entity	period	per	dummy
A	1	5	3
A	2	.	3
B	1	9	1
B	2	.	1
C	1	7	2
C	2	.	2
*/
data want (drop = custom_order);
  set have1;
  retain dummy 0;
  by entity period;
  
  if first.entity then dummy = custom_order;

run;
/* final, sorted want:
entity	period	per	dummy
B	1	9	1
B	2	.	1
C	1	7	2
C	2	.	2
A	1	5	3
A	2	.	3
*/
proc sort data = want; by dummy period ; run;

[PaigeMiller]

proc sort data=have;
    by descending entity period;
run;

[PavelD]

Hi PaigeMiller, sorry I have given maybe a wrong example. It is a coincidence that entity is nicely sorted in the "want" dataset. Let me edit.

[Reeza · Accepted solution]

Since you want a custom order, map B to 1, C to 2 and A to 3 and then sort by that column. You can either keep it as a variable or drop if you don't need it.

[FreelanceReinh]

---

@PavelD wrote:

Want: grouped by entity, these groups then sorted by per. So the highest per at the top. Per is missing when period=2.

 edit 2018-12-10 16:26: clarification (entity order in "want" is not important, but they need to be grouped)

---

proc sort data=have;
by entity descending per;
run;

[PavelD]

Thank you @Reeza, I see your point (sorry for not replying to you directly, I wrote it on the phone and there is no "reply to" option).

[Reeza]

You replied to your own message so not sure who you're replying to here or what you're trying to say.