BY Group -- reorder within BY group based on condition

[ChrisBrooks]

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

[Zeus_Olympous]

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.

[RW9 · Accepted solution]

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;

[Zeus_Olympous]

Dear RW9,

BRILLIANT!

It works like charm.

Thanks a MILLION!

[RW9]

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.

[Zeus_Olympous]

Thanks...Good point!