topic Re: Sorting and grouping across more columns with missing data in SAS Procedures

Sorting and grouping across more columns with missing data

PavelD — Tue, 11 Dec 2018 13:15:17 GMT

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;

Re: Sorting and grouping across more columns with missing data

PaigeMiller — Mon, 10 Dec 2018 15:24:12 GMT

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

Re: Sorting and grouping across more columns with missing data

PavelD — Mon, 10 Dec 2018 15:25:52 GMT

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.

Re: Sorting and grouping across more columns with missing data

Reeza — Mon, 10 Dec 2018 16:09:12 GMT

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.

Re: Sorting and grouping across more columns with missing data

FreelanceReinh — Mon, 10 Dec 2018 16:25:26 GMT

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

Re: Sorting and grouping across more columns with missing data

PavelD — Tue, 11 Dec 2018 06:49:37 GMT

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).

Re: Sorting and grouping across more columns with missing data

Reeza — Mon, 10 Dec 2018 16:58:16 GMT

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