topic Re: Case when in SAS Programming

Case when

anonymous_user — Tue, 28 Jun 2016 13:33:03 GMT

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.

Re: Case when

Loko — Tue, 28 Jun 2016 13:41:02 GMT

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;

Re: Case when

anonymous_user — Tue, 28 Jun 2016 13:45:50 GMT

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

Re: Case when

Reeza — Tue, 28 Jun 2016 13:58:51 GMT

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.

Re: Case when

LinusH — Wed, 29 Jun 2016 12:44:51 GMT

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

Are you building an analytical base table?