DATA Step, Macro, Functions and more

BY Group -- reorder within BY group based on condition

Accepted Solution Solved
Reply
Contributor
Posts: 45
Accepted Solution

BY Group -- reorder within BY group based on condition

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;

Accepted Solutions
Solution
‎07-31-2017 08:59 AM
Super User
Super User
Posts: 7,971

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

Posted in reply to Zeus_Olympous

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


All Replies
PROC Star
Posts: 759

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

Posted in reply to Zeus_Olympous

Why do you want this ordering of your data?

Super User
Super User
Posts: 7,971

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

Posted in reply to Zeus_Olympous

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;
Contributor
Posts: 45

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

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.
Super User
Super User
Posts: 7,971

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

Posted in reply to Zeus_Olympous

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.

Regular Contributor
Posts: 234

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

[ Edited ]
Posted in reply to Zeus_Olympous

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;
Contributor
Posts: 45

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

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.
Regular Contributor
Posts: 234

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

Posted in reply to Zeus_Olympous
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 ?
Contributor
Posts: 45

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

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;
Contributor
Posts: 45

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

Posted in reply to Zeus_Olympous
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.
Super User
Super User
Posts: 7,971

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

Posted in reply to Zeus_Olympous

Capture.PNG

Just below the word Preview in the attached image.

Contributor
Posts: 45

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

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

 

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;

 

 

 

Super User
Super User
Posts: 7,971

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

Posted in reply to Zeus_Olympous

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.

Contributor
Posts: 45

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

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

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

 

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.

 

Regular Contributor
Posts: 234

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

[ Edited ]
Posted in reply to Zeus_Olympous

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.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 20 replies
  • 124 views
  • 2 likes
  • 5 in conversation