Proc Sql case confusion

Question

Within SAS

I have a proc-sql step that I'm using to create macro variables to do some list processing.

I have ran into a confusing step where using a case statement rather than a where statement results in the first row of the resulting data set being a null string ('')

There are no null strings contained in either field in either table.

These are two sample SQL steps with all of the macro business removed for simplicity:

    create table test as
    select distinct 
                case 
                    when brand in (select distinct core_brand from new_tv.core_noncore_brands) then brand
                end as brand1
    from new_tv.new_tv2
    ;

    create table test2 as
    select distinct brand
    from new_tv.new_tv2
    where brand in (select distinct core_brand from new_tv.core_noncore_brands)
    ; 

using the first piece of code the result is a table with multiple rows, the first being an empty string.

The second piece of code works as expected

Any reason for this?

Answer 1

So the difference is that without a WHERE clause you aren't limiting what you are selecting, IE every row is considered. The CASE statement can bucket items by criteria, but you don't lose results just because your buckets don't catch everything, hence the NULL. WHERE limits the items being returned.

Answer 2

Yes, the first has no then clause in the case statement. I'm surprised that it even parses. It wouldn't in many SQL dialects.

Presumably you mean:

create table test as
select distinct 
            case 
                when brand in (select distinct core_brand from new_tv.core_noncore_brands) 
                then brand
            end as brand1
from new_tv.new_tv2
;

The reason you are getting the NULL is because the case statement is return NULL for the non-matching brands. You would need to add:

where brand1 is not NULL

to prevent this (using either a subquery or making brand1 a calculated field).

Answer 3

Your first query is not correct, there is no 'then' statement in the 'case' clause.

create table test as
    select distinct 
                case 
                    when brand in (select distinct core_brand from new_tv.core_noncore_brands) 

*then value*                
end as brand1
    from new_tv.new_tv2
    ;

Probably, you have NULL value because there is no default value for the 'case' clause, so for the value which doesn't meet the condition it returns NULL. There is a difference between 'case' clause and 'NOT IN', the first returns you all the rows, but without values, which do not meet condition, when second query will return only row which meet condition.