Solved: Re: BY Group -- reorder within BY group based on condition

Zeus_Olympous · Posted 07-31-2017 05:36 AM

Dear all, I have the DATA SET HAVE with by groups as shown. I want dataset WANT where the by group is ordered as shown (I need them ordered by date as well). I cannot figure the right variable sort ordering. Any hint/suggestion would be more than welcome. Thank you. data HAVE; group=1; j=46; t='FD'; m='20Dec2016'; output; group=1; j=46; t='FD'; m='20Dec2016'; output; group=1; j=46; t='FD'; m='20Dec2016'; output; group=1; j=46; t='FD'; m='20Dec2016'; output; group=1; j=52; t='KL'; m='03Jan2015'; output; group=1; j=52; t='KL'; m='03Jan2015'; output; group=1; j=52; t='KL'; m='03Jan2015'; output; group=1; j=52; t='KL'; m='03Jan2015'; output; group=1; j=99; t='LL'; m='16Mar2016'; output; group=1; j=99; t='LL'; m='16Mar2016'; output; group=1; j=99; t='LL'; m='16Mar2016'; output; group=1; j=99; t='LL'; m='16Mar2016'; output; group=1; j=11; t='AA'; m='30Sep2016'; output; group=1; j=11; t='AA'; m='30Sep2016'; output; group=1; j=11; t='AA'; m='30Sep2016'; output; group=1; j=11; t='AA'; m='30Sep2016'; output; run; data WANT; group=1; j=52; t='KL'; m='03Jan2015'; output; group=1; j=99; t='LL'; m='16Mar2016'; output; group=1; j=11; t='AA'; m='30Sep2016'; output; group=1; j=46; t='FD'; m='20Dec2016'; output; group=1; j=52; t='KL'; m='03Jan2015'; output; group=1; j=99; t='LL'; m='16Mar2016'; output; group=1; j=11; t='AA'; m='30Sep2016'; output; group=1; j=46; t='FD'; m='20Dec2016'; output; group=1; j=52; t='KL'; m='03Jan2015'; output; group=1; j=99; t='LL'; m='16Mar2016'; output; group=1; j=11; t='AA'; m='30Sep2016'; output; group=1; j=46; t='FD'; m='20Dec2016'; output; group=1; j=52; t='KL'; m='03Jan2015'; output; group=1; j=99; t='LL'; m='16Mar2016'; output; group=1; j=11; t='AA'; m='30Sep2016'; output; group=1; j=46; t='FD'; m='20Dec2016'; output; run;

RW9 · Posted 07-31-2017 08:38 AM

Well, it doesn't make sense, but its still the same, you just need to identify order variables:

data HAVE;
group=1; j=46; t='FD'; m='20Dec2016'; output;
group=1; j=46; t='FD'; m='20Dec2016'; output;
group=1; j=46; t='FD'; m='20Dec2016'; output;
group=1; j=46; t='FD'; m='20Dec2016'; output;
group=1; j=52; t='KL'; m='03Jan2015'; output;
group=1; j=52; t='KL'; m='03Jan2015'; output;
group=1; j=52; t='KL'; m='03Jan2015'; output;
group=1; j=52; t='KL'; m='03Jan2015'; output;
group=1; j=99; t='LL'; m='16Mar2016'; output;
group=1; j=99; t='LL'; m='16Mar2016'; output;
group=1; j=99; t='LL'; m='16Mar2016'; output;
group=1; j=99; t='LL'; m='16Mar2016'; output;
group=1; j=11; t='AA'; m='30Sep2016'; output;
group=1; j=11; t='AA'; m='30Sep2016'; output;
group=1; j=11; t='AA'; m='30Sep2016'; output;
group=1; j=11; t='AA'; m='30Sep2016'; output;
run;
 
proc sort data=have;
  by group t;
run;

data want;
  set have;
  retain ord2 1;
  by group t;
  ord2=ifn(first.t,1,ord2+1);
  select(t);
    when("KL") ord1=1;
    when("LL") ord1=2;
    when("AA") ord1=3;
    otherwise ord1=4;
  end;
run;

proc sort data=want;
  by group ord2 ord1;
run;

View solution in original post

PeterClemmensen · Posted 07-31-2017 05:41 AM

Why do you want this ordering of your data?

The DATA to DATA Step Macro
Blog: SASnrd

RW9 · Posted 07-31-2017 05:43 AM

When posting code use the {i} above the post area to preserve formatting and such like, otherwise it is just unreadable. From what I gather you are having trouble sorting based on the text FD, KL etc. As there appears to be no logical ordering, I would suggest you create a variable specifically for ordering and use that rather than the text field, maybe something like:

data have;
  set have;
  select(t);
    when("KL") then ord=1;
    when("FD") then ord=2;
...
  end;
run;
proc sort data=have out=want;
  by group ord m;
run;

Zeus_Olympous · Posted 07-31-2017 06:20 AM

Dear RW9 and gammotee , I tried both solutions, neither orders the wanted way (both move around internally the subgroups i.e. "KL3, "FD" ) . Thank you.

RW9 · Posted 07-31-2017 06:29 AM

So please clarify exactly what it is you want. Your title - reorder within by group based on condition - indicates that you want the data in a different, non-logical order, thus I present an eample method to do this by creating a logical order variable to sort by.

gamotte · Posted 07-31-2017 05:56 AM

You can do it in a single step with proc sql :

proc sql noprint;
  CREATE TABLE want AS
  SELECT *
  FROM have
  ORDER BY CASE WHEN t="KL" THEN 1
                WHEN t="FD" THEN 2
                ...
           ELSE <n> END;
quit;

Zeus_Olympous · Posted 07-31-2017 06:19 AM

Dear RW9 and gammotee , I tried both solutions, neither orders the wanted way (both move around internally the subgroups i.e. "KL3, "FD" ) . Thank you.

gamotte · Posted 07-31-2017 06:30 AM

I could not download the joint file because it is blocked by my employer's proxy and your initial post is unreadable for the reason pointed out by RW9. What should be the desired order ?

Zeus_Olympous · Posted 07-31-2017 06:51 AM

For some reason I am not able to see the symbol that RW9 suggests. I will try to repost the HAVE and WANT, I hope that they will make sense. Thank you. data HAVE; group=1; j=46; t='FD'; m='20Dec2016'; output; group=1; j=46; t='FD'; m='20Dec2016'; output; group=1; j=46; t='FD'; m='20Dec2016'; output; group=1; j=46; t='FD'; m='20Dec2016'; output; group=1; j=52; t='KL'; m='03Jan2015'; output; group=1; j=52; t='KL'; m='03Jan2015'; output; group=1; j=52; t='KL'; m='03Jan2015'; output; group=1; j=52; t='KL'; m='03Jan2015'; output; group=1; j=99; t='LL'; m='16Mar2016'; output; group=1; j=99; t='LL'; m='16Mar2016'; output; group=1; j=99; t='LL'; m='16Mar2016'; output; group=1; j=99; t='LL'; m='16Mar2016'; output; group=1; j=11; t='AA'; m='30Sep2016'; output; group=1; j=11; t='AA'; m='30Sep2016'; output; group=1; j=11; t='AA'; m='30Sep2016'; output; group=1; j=11; t='AA'; m='30Sep2016'; output; run; data WANT; group=1; j=52; t='KL'; m='03Jan2015'; output; group=1; j=99; t='LL'; m='16Mar2016'; output; group=1; j=11; t='AA'; m='30Sep2016'; output; group=1; j=46; t='FD'; m='20Dec2016'; output; group=1; j=52; t='KL'; m='03Jan2015'; output; group=1; j=99; t='LL'; m='16Mar2016'; output; group=1; j=11; t='AA'; m='30Sep2016'; output; group=1; j=46; t='FD'; m='20Dec2016'; output; group=1; j=52; t='KL'; m='03Jan2015'; output; group=1; j=99; t='LL'; m='16Mar2016'; output; group=1; j=11; t='AA'; m='30Sep2016'; output; group=1; j=46; t='FD'; m='20Dec2016'; output; group=1; j=52; t='KL'; m='03Jan2015'; output; group=1; j=99; t='LL'; m='16Mar2016'; output; group=1; j=11; t='AA'; m='30Sep2016'; output; group=1; j=46; t='FD'; m='20Dec2016'; output; run;

Zeus_Olympous · Posted 07-31-2017 06:55 AM

Unfortunately the problem persists.. Any way the contents of the first dataset (HAVE ) start from "data HAVE;" and end to the first "run;" The contents of the wanted dataset start from "data WANT;" and end to the second "run;"...I think it will work if copied and paste to SAS workspace. Thank you.

RW9 · Posted 07-31-2017 06:59 AM

Just below the word Preview in the attached image.

Zeus_Olympous · Posted 07-31-2017 07:40 AM

Ok ..now it seems that works...please see attached HAVe and WANT. Actually I need a way to reorder the by group observations at will.

Thank you.

data HAVE;

group=1; j=46; t='FD'; m='20Dec2016'; output;

group=1; j=52; t='KL'; m='03Jan2015'; output;

group=1; j=99; t='LL'; m='16Mar2016'; output;

group=1; j=11; t='AA'; m='30Sep2016'; output;

run;

WANT

data WANT;

group=1; j=52; t='KL'; m='03Jan2015'; output;

group=1; j=99; t='LL'; m='16Mar2016'; output;

group=1; j=11; t='AA'; m='30Sep2016'; output;

group=1; j=46; t='FD'; m='20Dec2016'; output;

group=1; j=52; t='KL'; m='03Jan2015'; output;

group=1; j=99; t='LL'; m='16Mar2016'; output;

group=1; j=11; t='AA'; m='30Sep2016'; output;

group=1; j=46; t='FD'; m='20Dec2016'; output;

group=1; j=52; t='KL'; m='03Jan2015'; output;

group=1; j=99; t='LL'; m='16Mar2016'; output;

group=1; j=11; t='AA'; m='30Sep2016'; output;

group=1; j=46; t='FD'; m='20Dec2016'; output;

group=1; j=52; t='KL'; m='03Jan2015'; output;

group=1; j=99; t='LL'; m='16Mar2016'; output;

group=1; j=11; t='AA'; m='30Sep2016'; output;

group=1; j=46; t='FD'; m='20Dec2016'; output;

run;

RW9 · Posted 07-31-2017 07:56 AM

Ok, so from what I see you posted there create a variable called ord, and set it to 1,2,3 or 4 depending on t variable, then sort by that:

data have
  set have;
  select(t);
    when "KL" then ord=1;
    when "LL" then ord=2;
    when "AA" then ord=3;
    else ord=4;
  end;
run;
proc sort data=have;
  by group ord;
run;

And you can order at will, however as there is no logical sort approach here, you need to add that into the data.

Zeus_Olympous · Posted 07-31-2017 08:09 AM

Dear RW9 ,

Unfortunately it does not do the trick.

As per your suggestion I used

data HAVE;

group=1; j=46; t='FD'; m='20Dec2016'; output;

group=1; j=52; t='KL'; m='03Jan2015'; output;

group=1; j=99; t='LL'; m='16Mar2016'; output;

group=1; j=11; t='AA'; m='30Sep2016'; output;

run;

data want;

set have;

select(t);

when ("KL") ord=1;

when ("LL") ord=2;

when ("AA") ord=3;

when ("FD") ord=4;

otherwise ord=5;

/*** JUST a note otherwise should relace end ***/

end;

run;

proc sort data=want;

by group ord;

run;

and I get

group j t m ord
1 52 KL 03Jan2015 1
1 52 KL 03Jan2015 1
1 52 KL 03Jan2015 1
1 52 KL 03Jan2015 1

1 99 LL 16Mar2016 2
1 99 LL 16Mar2016 2
1 99 LL 16Mar2016 2
1 99 LL 16Mar2016 2

1 11 AA 30Sep2016 3
1 11 AA 30Sep2016 3
1 11 AA 30Sep2016 3
1 11 AA 30Sep2016 3

1 46 FD 20Dec2016 4
1 46 FD 20Dec2016 4
1 46 FD 20Dec2016 4
1 46 FD 20Dec2016 4

while I want

group j t m
1 52 KL 03Jan2015
1 99 LL 16Mar2016
1 11 AA 30Sep2016
1 46 FD 20Dec2016

1 52 KL 03Jan2015
1 99 LL 16Mar2016
1 11 AA 30Sep2016
1 46 FD 20Dec2016

Thanking you in advance.

gamotte · Posted 07-31-2017 08:17 AM

It seems that the same sample is repeated 4 times. Is it always the case ? If so, there is no need to

store redundant information. Perform the custom sort on the dataset using either proposed method and

eliminate duplicates.

SAS Innovate 2025: Save the Date

SAS Training: Just a Click Away