topic Re: BY Group -- reorder within BY group based on condition in SAS Programming

BY Group -- reorder within BY group based on condition

Zeus_Olympous — Mon, 31 Jul 2017 09:36:03 GMT

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;

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

PeterClemmensen — Mon, 31 Jul 2017 09:41:12 GMT

Why do you want this ordering of your data?

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

RW9 — Mon, 31 Jul 2017 09:43:25 GMT

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;

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

gamotte — Mon, 31 Jul 2017 09:57:39 GMT

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;

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

Zeus_Olympous — Mon, 31 Jul 2017 10:19:24 GMT

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.

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

Zeus_Olympous — Mon, 31 Jul 2017 10:20:07 GMT

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.

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

RW9 — Mon, 31 Jul 2017 10:29:51 GMT

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.

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

gamotte — Mon, 31 Jul 2017 10:30:18 GMT

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 ?

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

Zeus_Olympous — Mon, 31 Jul 2017 10:51:59 GMT

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;

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

Zeus_Olympous — Mon, 31 Jul 2017 10:55:32 GMT

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.

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

RW9 — Mon, 31 Jul 2017 10:59:53 GMT

Just below the word Preview in the attached image.

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

Zeus_Olympous — Mon, 31 Jul 2017 11:40:31 GMT

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;

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

RW9 — Mon, 31 Jul 2017 11:56:26 GMT

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.

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

Zeus_Olympous — Mon, 31 Jul 2017 12:09:26 GMT

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.

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

gamotte — Mon, 31 Jul 2017 12:17:50 GMT

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.

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

ChrisBrooks — Mon, 31 Jul 2017 12:26:26 GMT

Another problem is you're storing a date as a text value which means when you sort it 20Dec2016 is coming before 30Sep2016. Also as mentioned holding duplicate key values is a very bad idea which will cause no end of problems further down the processing track....

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

RW9 — Mon, 31 Jul 2017 12:38:25 GMT

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;

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

Zeus_Olympous — Mon, 31 Jul 2017 12:38:38 GMT

Dear Chris,

Thank you for your responce.

You are correct .

Allow me to clarify.

Although In the original dataset the date variable is stored in SAS date numeric values.

I convert it to "character" to my example just to highlight my request.

Still I am searching the net and I have found nothing relevant.

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

Zeus_Olympous — Mon, 31 Jul 2017 12:41:00 GMT

Dear RW9,

BRILLIANT!

It works like charm.

Thanks a MILLION!

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

RW9 — Mon, 31 Jul 2017 12:54:11 GMT

Looking at your response to Chris, it may be worth looking at formats rather than creating a variable:

proc format;
  value myfmt
    1="KL"
    2="LL"
...
run;

That way you keep your numeric data, can order correctly, and display text replacement when needed.