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;
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;
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;
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.
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.