DATA Step, Macro, Functions and more

Case when

Reply
Frequent Contributor
Posts: 140

Case when

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.

Super Contributor
Posts: 305

Re: Case when

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;
Frequent Contributor
Posts: 140

Re: Case when

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

Super User
Posts: 17,963

Re: Case when

[ Edited ]

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. 

Super User
Posts: 5,260

Re: Case when

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

Are you building an analytical base table?

Data never sleeps
Ask a Question
Discussion stats
  • 4 replies
  • 233 views
  • 0 likes
  • 4 in conversation