topic Re: Reordering groups when condition is met in SAS Programming

Reordering groups when condition is met

Miracle — Wed, 11 Nov 2020 11:34:17 GMT

Dear All,
Can I please seek your help on how to achieve this?

When group=ZZZ has the same freq to other groups (in this case group=XYZ and STU which will change depending on the time point of data extraction) we are required to always present group=ZZZ as the last group before the next group with reduced freq (in this case, MNO 30).
Thanks a lot.
Let say the following is my input data.

group	freq
ABC	100
CDE	50
FGH	45
IJK	46
ZZZ	43
XYZ	43
STU	43
MNO	30

How do I achieve the following the data? [PREVIOUS]

group	freq
ABC	100
CDE	50
FGH	45
IJK	46
XYZ	43
STU	43
ZZZ	43
MNO	30

How do I achieve the following the data? [CURRENT]

group	freq
ABC	100
CDE	50
FGH	45
IJK	46
XYZ	43
STU	43
ZZZ	43
MNO	30
BBC	30
CCN	30
JKL	30

Re: Reordering groups when condition is met

PeterClemmensen — Mon, 09 Nov 2020 10:05:19 GMT

Like this?

data have;
input group $ 1-3 freq;
datalines;
ABC	100
CDE 50 
FGH 45 
IJK 46 
ZZZ	43 
XYZ	43 
STU	43 
MNO 30 
;

proc sql;
   create table want as
   select * from have
   order by freq desc, group = "ZZZ";
quit;

Result

group freq 
ABC   100 
CDE   50 
IJK   46 
FGH   45 
STU   43 
XYZ   43 
ZZZ   43 
MNO   30

Re: Reordering groups when condition is met

Kurt_Bremser — Mon, 09 Nov 2020 10:07:56 GMT

Given your group names, this is the quickest way:

proc sort
  data=have
  out=want
;
by descending freq group;
run;

Re: Reordering groups when condition is met

Miracle — Mon, 09 Nov 2020 12:52:19 GMT

Thanks @PeterClemmensen and @Kurt_Bremser for your quick help 😊

Re: Reordering groups when condition is met

Miracle — Wed, 11 Nov 2020 11:38:54 GMT

Hi @PeterClemmensen.
Thanks but it doesn't work for current case.
How do we do achieve given the current case please?

Re: Reordering groups when condition is met

Kurt_Bremser — Wed, 11 Nov 2020 11:53:07 GMT

You need to supply the source data for your second expected result. Since we have shown you how to do it, supply it as a data step with datalines.

Re: Reordering groups when condition is met

Miracle — Wed, 11 Nov 2020 14:22:12 GMT

Hi @Kurt_Bremser .
Thanks for your quick response.
It's OK now.
I managed to get the desired output by doing some slight adjustment in datastep and using proc sort.