topic Sorting Text Columns in SAS Procedures

Sorting Text Columns

altijani — Wed, 09 May 2018 16:56:42 GMT

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

Re: Sorting Text Columns

Kurt_Bremser — Wed, 09 May 2018 17:30:08 GMT

This

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

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

Re: Sorting Text Columns

altijani — Wed, 09 May 2018 17:33:28 GMT

Hi,

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

Thanks,

Altijani

Re: Sorting Text Columns

mkeintz — Wed, 09 May 2018 17:47:02 GMT

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;

Re: Sorting Text Columns

Ksharp — Thu, 10 May 2018 13:15:18 GMT

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;