SAS SQL - CASE STATEMENT

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 122
Accepted Solution

SAS SQL - CASE STATEMENT

To whom it may concern,

I am using the query below to convert the Loan_Categories into the Subtotals dataset; both datasets can be found in the attached Example file. Why does my query not work?

proc sql;

  create table Subtotals as

  select

  case when CE_Category like 'RECOURSE%' then 'SubTotal(Recourse)'

      else when CE_Category like 'INDEMN%' then 'SubTotal(Indemnification)'

      else when CE_Category like 'MIPOOL%' then 'SubTotal(MIPool)'

      else when CE_Category like 'No CE%' then 'SubTotal(No CE)'

  end as CE_Category,

  purchased_loan_count,

  purchased_upb_amount

from Loan_categories;

quit;


Accepted Solutions
Solution
‎09-04-2013 01:09 PM
Super User
Posts: 19,822

Re: SAS SQL - CASE STATEMENT

Posted in reply to maroulator

Run a proc print on your data set and see the exact categories, usually an extra space somewhere or wrong case, for example in the actual data is it NO or No?

You can try upcase(compress(ce_category)) like 'NOCE%' to see if that helps.

You may want to add an else clause as well:

proc sql;

  create table Subtotals as

  select

  case when CE_Category like 'RECOURSE%' then 'SubTotal(Recourse)'

      when CE_Category like 'INDEMN%' then 'SubTotal(Indemnification)'

     when CE_Category like 'MIPOOL%' then 'SubTotal(MIPool)'

      when upcase(compress(CE_Category)) like 'NOCE%' then 'SubTotal(No CE)'

     else 'CHECK ME'

  end as CE_Category,

  purchased_loan_count,

  purchased_upb_amount

from Loan_categories;

quit;

Message was edited by: Reeza

View solution in original post


All Replies
Solution
‎09-04-2013 01:09 PM
Super User
Posts: 19,822

Re: SAS SQL - CASE STATEMENT

Posted in reply to maroulator

Run a proc print on your data set and see the exact categories, usually an extra space somewhere or wrong case, for example in the actual data is it NO or No?

You can try upcase(compress(ce_category)) like 'NOCE%' to see if that helps.

You may want to add an else clause as well:

proc sql;

  create table Subtotals as

  select

  case when CE_Category like 'RECOURSE%' then 'SubTotal(Recourse)'

      when CE_Category like 'INDEMN%' then 'SubTotal(Indemnification)'

     when CE_Category like 'MIPOOL%' then 'SubTotal(MIPool)'

      when upcase(compress(CE_Category)) like 'NOCE%' then 'SubTotal(No CE)'

     else 'CHECK ME'

  end as CE_Category,

  purchased_loan_count,

  purchased_upb_amount

from Loan_categories;

quit;

Message was edited by: Reeza

Super User
Super User
Posts: 7,060

Re: SAS SQL - CASE STATEMENT

Too many else keywords.  You can only have one ELSE in a CASE statement.

Super User
Posts: 19,822

Re: SAS SQL - CASE STATEMENT

True Smiley Happy

Super Contributor
Posts: 307

Re: SAS SQL - CASE STATEMENT

Posted in reply to maroulator

You are trying to RE-summarize the data so that there is only 4 categories (RECOURSE, INDEMN, MIPOOL, NO CE)??? Try this:

proc sql;

  create table Subtotals as

  select

  ( case

      when CE_Category like 'RECOURSE%' then 'SubTotal(Recourse)'

      when CE_Category like 'INDEMN%' then 'SubTotal(Indemnification)'

      when CE_Category like 'MIPOOL%' then 'SubTotal(MIPool)'

      else 'SubTotal(No CE)'

  end ) as CE_Category,

  sum ( purchased_loan_count ) as purchased_loan_count,

  sum ( purchased_upb_amount ) as purchased_upb_amount

from Loan_categories

group by CE_Category

;

quit;

Super User
Posts: 19,822

Re: SAS SQL - CASE STATEMENT

I don't think its good to use else like that in a case statement. Smiley Happy

Super Contributor
Posts: 307

Re: SAS SQL - CASE STATEMENT

@Reeza "I don't think it's good to use else like that in a case statement."

I don't think I follow you. The else statement that I provided is based on the categories provided by maroulator. With the original categories as enumerated, the statement will work as expected.


Super User
Posts: 19,822

Re: SAS SQL - CASE STATEMENT

IMO, Its one of those best practices things, allowing categories to be coded by an implicit condition rather than explicitly assigning the category. Things change over time and I've seen that exact condition lead to errors that costed millions of dollars.  That's why my "else" is usually an error note.

Super Contributor
Posts: 307

Re: SAS SQL - CASE STATEMENT

I understand and, in one sense, I agree with you. However, the original question and data provided was fairly clear. I assumed that the poster understood the unique values of CE_CATEGORY, and that there were no other possible values besides the sample provided. There are various strategies that could be deployed to deal with unknown categories. A simple approach would be:

proc sql;

  create table Subtotals as

  select

  ( case

      when CE_Category like 'RECOURSE%' then 'SubTotal(Recourse)'

      when CE_Category like 'INDEMN%' then 'SubTotal(Indemnification)'

      when CE_Category like 'MIPOOL%' then 'SubTotal(MIPool)'

      when CE_Category like 'No CE%' then 'SubTotal(No CE)'

      else 'SubTotal(All Others)'

  end ) as CE_Category,

  sum ( purchased_loan_count ) as purchased_loan_count,

  sum ( purchased_upb_amount ) as purchased_upb_amount

from Loan_categories

group by CE_Category

;

quit;

Of course, this could be expanded to produce automated reporting, message alerts or e-mails if there are categories other than what we expect.

Frequent Contributor
Posts: 122

Re: SAS SQL - CASE STATEMENT

Posted in reply to maroulator

Thanks for the help everyone!! Always great to see a fantastic support network at work; extremely helpful response!

🔒 This topic is solved and locked.

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

Discussion stats
  • 9 replies
  • 77718 views
  • 11 likes
  • 4 in conversation