How do I rank text strings and sort?

Accepted Solution Solved
Reply
Contributor
Posts: 36
Accepted Solution

How do I rank text strings and sort?

Dear fellow SAS users,

 

I am stuck with a kind of string process problem, and would greatly appreciate if you can help me.

 

My problem is to use a hierchy of strings to select.

 

Suppose I have the following data:

 

Customer   Score1   Score2

John           A            B

John           A            A

John           B            B

Ann            C            A

Ann            B            B

Mary          C            B

Mary          B            C

 

 

Suppose, the Score1 and Score2 are strings, and there is a hierchy A>B>C.

 

Now I want to select the highest ever scored Score1 and Score2 into a unique row per Customer, and the result should look like this:

Customer  Score1     Score2

John          A              A

Ann           B              A

Mary         B              B

 

How should I program this. It will be easier if both Score1 and Score2 are sorted numerics, but the data actually are strings with text phrases. I need to rank those phrases per Customer.

 

Thanks for your help.

Ken

 

 


Accepted Solutions
Solution
‎06-02-2016 09:51 AM
Super User
Posts: 5,071

Re: How do I rank text strings and sort?

In SAS, PROC SQL lets you apply the MIN function to character strings.

View solution in original post


All Replies
Super User
Posts: 9,662

Re: How do I rank text strings and sort?

data have;
input (Customer   Score1   Score2) ($);
cards;
John           A            B
John           A            A
John           B            B
Ann            C            A
Ann            B            B
Mary          C            B
Mary          B            C
;
run;

proc sql;
 select Customer,min(Score1) as Score1,min(Score2) as Score2
  from have
   group by Customer ;
quit;
Contributor
Posts: 36

Re: How do I rank text strings and sort?

Dear KSharp,

 

Thank you very much for your help. But that is exactly my problem. Had it been so that Score1 and Score2 are numerics, so that I could use Min or Max functions, I would have solved it much easily.

 

The problem is, Score1 and Score2 are not numeric, in fact, A, B, C are already simplistic. In reality, they can be something like Foundation, Advance, Standard, and so on. They are in text strings, and I have to define a hierchy of those strings into something like:

A>B>C, or Advance1>Foundation>Standard.

And then use this hierchy to rank them...Of course, I can assign a numeric hierchy to those strings, but there must be some sexier way of doing it.

 

Thanks for your help.

Ken

Super User
Super User
Posts: 7,392

Re: How do I rank text strings and sort?

You could start by posting some actual test data (as a datastep) which reflects your problem.  You will likely find it easier to recode or apply a format to your text data, e.g.

data want;
  set your_data;
  select(result1);
    when "First result" res1=1;
    when...;
  end;
  select(result2);
  ...
run;

You may be lucky if the first character is always in sequence, then you could just take the ASCII value of the character, however I would still suggest re-coding the values - makes maintenance easier if later on another grade is given and doesn't fit your logic.

Contributor
Posts: 36

Re: How do I rank text strings and sort?

Thanks for the advice!! Greatly appreciated.

Solution
‎06-02-2016 09:51 AM
Super User
Posts: 5,071

Re: How do I rank text strings and sort?

In SAS, PROC SQL lets you apply the MIN function to character strings.

Contributor
Posts: 36

Re: How do I rank text strings and sort?

I didn't know that. I will now test it...thanks!!

Super User
Posts: 9,662

Re: How do I rank text strings and sort?

Well . You can pad some white blanks before it to do this ranking thing.

 

data have;
input (Customer   Score1   Score2) ($);
cards;
John           A            B
John           A            A
John           B            B
Ann            C            A
Ann            B            B
Mary          C            B
Mary          B            C
;
run;

data have;
 set have;
 array x{*} $ Score:;
 do i=1 to dim(x);
  if x{i}='A' then x{i}='  A';
   else if x{i}='B' then x{i}=' B';
 end;
run;

proc sql;
 select Customer,min(Score1) as Score1,min(Score2) as Score2
  from have
   group by Customer ;
quit;
Contributor
Posts: 36

Re: How do I rank text strings and sort?

Thanks I will test your code...

Contributor
Posts: 36

Re: How do I rank text strings and sort?

Thank you! Proc SQL actually takes char into min max function! That solves the problem! Of course, one must follow the char alphabetic order, which involves recoding the score1 and score2...

Thanks!!!

Trusted Advisor
Posts: 1,115

Re: How do I rank text strings and sort?

Hi Ken,

 

Here's an option without recoding:

%let sc='C', 'B', 'A'; /* sorted from lowest to highest */

proc sql;
create table want as
select Customer, choosec(max(whichc(Score1, &sc)), &sc) as Score1 length=1,
                 choosec(max(whichc(Score2, &sc)), &sc) as Score2 length=1
from have
group by Customer;
quit;

I used MAX rather than MIN, because the minimum could easily be a missing or invalid score. The LENGTH= specifications (to be increased for longer text phrases) override the default length of 200.

☑ This topic is SOLVED.

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

Discussion stats
  • 10 replies
  • 533 views
  • 1 like
  • 5 in conversation