Case when

[#1]

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.

[Loko]

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;

[#3]

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

[Reeza]

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. 

[LinusH]

SQl works best with normalized data. Havin columns named appN etc isn't normalized.

Are you building an analytical base table?