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?
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.