Sorting Text Columns

[altijani]

Hi Community,

I have the following unsorted data:

Month   Variable1

Jan        123456789

Feb        123456789

Mar        123456789

Jan         123456-P

Jan         123456

Feb        123456-PCLOSED

Jan        123456CLOSED

Jan        987654321

Feb       123456789CLOSED

Jan        25648985

Mar       123456789CLOSED

Apr        123456789CLOSED

Which I need to sort in this order (each observation ending with CLOSED to be following the observation without CLOSED):

Month Variable1

Jan      123456

Feb      123456CLOSED

Jan      123456-P

Feb      123456-PCLOSED

Jan      123456789

Feb      123456789CLOSED

Feb      123456789

Mar      123456789CLOSED

Mar      123456789

Apr      123456789CLOSED

Jan      25648985

Jan      987654321

The aim of the sort is because I need to keep in my data the observations with the following conditions:

1. If the observation has both CLOSED and the previous without CLOSED at the end, keep the one with CLOSED. In other words, drop the observations that do not have CLOSED at the end

2. If the observation hasn't a CLOSED at the end and hasn't a replicate with CLOSED (like the last two observations from above), keep them as is

By the end, I need Variable1 to include only the following observations:

123456789CLOSED

123456-PCLOSED

123456CLOSED

987654321

25648985

Thanks

Altijani

[Kurt_Bremser]

This

each observation ending with CLOSED to be following the observation without CLOSED

does not match the first two lines of your "want" data.

[altijani]

Hi,

I am not sure I understand your question. I will create another data in excel and will post soon.

Thanks,

Altijani

[mkeintz · Accepted solution]

So you have a BASEID which sometimes is terminated with "CLOSE".  If it is you want to keep that record.  Otherwise you want to keep one copy of the ID without "CLOSED":

data have;
  input mon :$3.  id :$20.;
datalines;
Jan        123456789
Feb        123456789
Mar        123456789
Jan         123456-P
Jan         123456
Feb        123456-PCLOSED
Jan        123456CLOSED
Jan        987654321
Feb       123456789CLOSED
Jan        25648985
Mar       123456789CLOSED
Apr        123456789CLOSED
run;

data vneed / view=vneed;
  set have;
  baseid=tranwrd(id,"CLOSED","");
run;

proc sort data=vneed out=need;
  by baseid id; 
run;

data want (keep=id);
  set need;
  by baseid;
  if last.baseid;
run;

[Ksharp]

data have;
  input mon :$3.  id :$20.;
datalines;
Jan        123456789
Feb        123456789
Mar        123456789
Jan         123456-P
Jan         123456
Feb        123456-PCLOSED
Jan        123456CLOSED
Jan        987654321
Feb       123456789CLOSED
Jan        25648985
Mar       123456789CLOSED
Apr        123456789CLOSED
run;
data a b;
 set have;
 if find(id,'CLOSED') then output b;
  else output a;
run;
data a;
 set a;
 n+1;
run;
data b;
 set b;
 n+1;
run;
data want;
 set a b;
 by n;
run;