Help using Base SAS procedures

Sorting Text Columns

Accepted Solution Solved
Reply
Contributor
Posts: 46
Accepted Solution

Sorting Text Columns

[ Edited ]

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

  


Accepted Solutions
Solution
‎05-15-2018 07:25 AM
Trusted Advisor
Posts: 1,345

Re: Sorting Text Columns

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;

 

 

View solution in original post


All Replies
Super User
Posts: 10,280

Re: Sorting Text Columns

This

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

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor
Posts: 46

Re: Sorting Text Columns

Posted in reply to KurtBremser

Hi,

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

Thanks,

Altijani

 

Solution
‎05-15-2018 07:25 AM
Trusted Advisor
Posts: 1,345

Re: Sorting Text Columns

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;

 

 

Super User
Posts: 10,787

Re: Sorting Text Columns

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;
☑ This topic is solved.

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

Discussion stats
  • 4 replies
  • 194 views
  • 1 like
  • 4 in conversation