BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Zeus_Olympous
Obsidian | Level 7
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;
1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

20 REPLIES 20
PeterClemmensen
Tourmaline | Level 20

Why do you want this ordering of your data?

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Obsidian | Level 7
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
Diamond | Level 26 RW9
Diamond | Level 26

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
Rhodochrosite | Level 12

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
Obsidian | Level 7
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
Rhodochrosite | Level 12
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
Obsidian | Level 7
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
Obsidian | Level 7
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
Diamond | Level 26 RW9
Diamond | Level 26

Capture.PNG

Just below the word Preview in the attached image.

Zeus_Olympous
Obsidian | Level 7

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;

 

 

 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Obsidian | Level 7

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.

 

gamotte
Rhodochrosite | Level 12

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-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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