I have written this code but get an error, any idea why please?
proc sql;
create table work.score_2 as
select
*,
case
when no_of_applicants = 1 then app1_employment_status_code = 'E'
when no_of_applicants > 1 then app2_employment_status_code = 'E'
else 'null'
end as employment
from
work.score_1;
quit;
ERROR: Result of WHEN clause 3 is not the same data type as the preceding results.
hello,
the correct syntax shall be
proc sql;
create table work.score_2 as
select
*,
case
when no_of_applicants = 1 then 'E'
when no_of_applicants > 1 then 'E'
else 'null'
end as employment
from
work.score_1;
quit;
Maybe I am asking for the wrong thing as surely that will just assign an E on the no_of_applicants rather than look up to 2 seperate variables? So on app1_employment_status_code you can have 'S' 'R' 'U' or 'E' but that code won't pick any of those up will it?
I want it that
if no_of_applicants = 1 then app1_employment_status_code = E
if no_of_applicants > 1 then app2_employment_status_code = E
Case statements don't work that way. You'll need a case statement for each variable you want to create.
If you can use a data step that is possible.
SQl works best with normalized data. Havin columns named appN etc isn't normalized.
Are you building an analytical base table?
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.