SQL - Inquiry

To whom it may concern,

I am using the query below to convert the Loan_Categories into the Subtotals; both datasets can be found in the attached Reference 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;

Open in new window

Example.xlsx
maroulatorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

plusone3055Commented:
proc sql;
  create table Subtotals as
select INTO
  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;
0
Kevin CrossChief Technology OfficerCommented:
I am not a SAS expert, but I am familiar with the CREATE TABLE AS SELECT syntax from MySQL and that part looks correct. From the SAS documentation, the create table part looks correct also. Therefore, I think the issue is in the CASE statement.

In Microsoft SQL Server (T-SQL), the syntax is CASE WHEN x THEN y ELSE z END.

Therefore, you will need to change:
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

Open in new window


To:
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)' 
  end as CE_Category

Open in new window


You can exclude the ELSE statement, which will make CE_Category NULL if the other cases are not true. If your goal is to create a secondary case on the ELSE, then you want to do this:
CASE 
    WHEN {condition} THEN {value}
    ELSE 
        CASE WHEN {condition} THEN {value} ELSE {value} END
END

Open in new window


I hope that makes sense.

Best regards and happy coding,

Kevin

P.S. For the subtotals, it appears your intent is to merge the amount of (Primary MI) and (No Primary MI) into one subtotal. If that is the case, you will want to add SUM() or other appropriate aggregate into the query. You can either derive your original query and perform the aggregation in the outer query OR repeat the CASE statement in the GROUP BY of the original query.

For example:
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)' 
  end as CE_Category,
  sum(purchased_loan_count) as total_purchased_loan_count,
  sum(purchased_upb_amount) as total_purchased_upb_amount
from Loan_categories
group by 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)' 
  end;
quit;

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Aloysius LowCommented:
mwvisa1 is right in saying remove the 'else' from the 'when' lines and things should be fine... however, just to correct his group by statements, as the field CE_CATEGORY is already derived in the select statement, there's no need to repeat the entire logic once again (on the count of efficiency and maintenance)... just use the keyword 'calculated'

i.e.

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)' 
  end as CE_Category,
  sum(purchased_loan_count) as total_purchased_loan_count,
  sum(purchased_upb_amount) as total_purchased_upb_amount
from Loan_categories
group by calculated CE_Category;
quit;

Open in new window

0
maroulatorAuthor Commented:
Thanks to all; this has been an extremely helpful SQL/SAS session!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.