topic Re: Why is my case statement returning null values? in SAS Programming

Why is my case statement returning null values?

LMSSAS — Mon, 29 Aug 2022 13:23:20 GMT

hello - can anyone advise as to why I am getting null values from my case statement? My final output should contain a column that I have added titled "Product _Categories" and every obs should have "MA; MAPD; DSNP" as the Product_Categories. When I run the table with the case statement I get Null's. Thanks for taking a look and offering a suggestion

proc sql; create table RTS2023 as
select distinct 
*
from medicaresalesagencies3
where Certification_Status in ('Ready To Sell 2023')
;quit;

proc sql; alter table work.RTS2023
add Product_Categories1 char 14;
quit;

proc sql; create table RTS_Sunfire as
select distinct
*,
case 
when Product_Categories1 is null then "MA; MAPD; DSNP"
end as Product_Categories
from RTS2023
;quit;

Re: Why is my case statement returning null values?

PaigeMiller — Mon, 29 Aug 2022 13:48:01 GMT

I can't reproduce this with my data, the value "MA: MAPD; DSNP" get assigned properly.

Are there WARNINGs or ERRORs in the LOG? If so, show us the ENTIRE log for these three PROCs.

Why not this?

proc sql; create table RTS2023 as
select distinct *
from medicaresalesagencies3
where Certification_Status in ('Ready To Sell 2023')
;quit;

proc sql; 
create table RTS_Sunfire as
select distinct *,
"MA; MAPD; DSNP" as Product_Categories
from RTS2023
;quit;

or this?

proc sql; create table RTS2023 as
select distinct
*,"MA; MAPD; DSNP" as product_categories
from medicaresalesagencies3
where Certification_Status in ('Ready To Sell 2023')
;quit;

Re: Why is my case statement returning null values?

jklaverstijn — Mon, 29 Aug 2022 13:44:54 GMT

SAS does not really have a character NULL value as compared to any RDBMS. An empty string or missing value is its nearest cousin and often obeys NULL comparison, but often it does not. It is a common trap for young players.

If you rewrite IS NULL to = "" you will see it works.

proc sql; create table RTS_Sunfire as
select distinct
*,
case 
when Product_Categories1 = "" then "MA; MAPD; DSNP"
end as Product_Categories
from RTS2023
;quit;

Alternatively you could use the coalesce function here:

proc sql; create table RTS_Sunfire as
select distinct
*, coalesce(Product_Categories1, "MA; MAPD; DSNP") as Product_Categories
from RTS2023
;quit;

Hope this helps,

- Jan.

Re: Why is my case statement returning null values?

LMSSAS — Mon, 29 Aug 2022 13:54:57 GMT

Thank you, Yes the proc sql you recommended worked and was much cleaner and simpler, appreciate your time!!

proc sql; create table RTS_Sunfire as
select distinct *,
"MA; MAPD; DSNP" as Product_Categories
from RTS2023
;quit;

Re: Why is my case statement returning null values?

LMSSAS — Mon, 29 Aug 2022 13:55:09 GMT

Thank you!!

Re: Why is my case statement returning null values?

Tom — Mon, 29 Aug 2022 16:43:19 GMT

Your syntax

case  when Product_Categories1 is null then "MA; MAPD; DSNP"
end as Product_Categories

It will convert the blank values to that string with the semicolons and convert the non blank values to blank because you did not include an ELSE clause.

Example:

data test;
  length Product_Categories1 $14 ;
  input Product_Categories1 ;
cards;
.
Something
;

proc sql;
create table RTS_Sunfire as
select distinct
       *
     , case when Product_Categories1 is null then "MA; MAPD; DSNP"
       end as Product_Categories
from test
;
quit;

proc print;
run;

Results:

        Product_         Product_
OBS    Categories1      Categories

 1                    MA; MAPD; DSNP
 2      Something

You could fix the logic by adding the ELSE clause.

case  when Product_Categories1 is null then "MA; MAPD; DSNP"
else Product_Categories1 
end as Product_Categories

But it would be a lot easier to just use the COALESCE() function of SQL.

coalesce(Product_Categories,"MA; MAPD; DSNP") as Product_Categories