topic Re: Proc sql case when condition to create multiple variables vs data step in SAS Programming

Proc sql case when condition to create multiple variables vs data step

buddha_d — Sat, 13 Jan 2018 05:26:46 GMT

Sas Gurus,

I am trying to create this data step in proc sql with case when statement and I am getting syntax errors. Any idea how to solve this? Thanks in advance.

DATA NAMES;
INPUT full_name $20.;
if index(full_name, ' ') = 0 then do;
first_name = substr(full_name,1,8);
last_name = substr(full_name,9,9);
end;
else do;
first_name = scan(full_name,1,' ');
last_name = substr(full_name, index(full_name,' '));
end;

CARDS;

;

RUN;

Re: Proc sql case when condition to create multiple variables vs data step

PGStats — Sat, 13 Jan 2018 07:23:04 GMT

You could do this:


proc sql;
create table names as
select
    full_name,
    case when countw(full_name) = 1 then substr(full_name,1,8) else scan(full_name,1) end as first_name,
    case when countw(full_name) = 1 then substr(full_name,9,9) else scan(full_name,2) end as last_name
from have;
quit;

Re: Proc sql case when condition to create multiple variables vs data step

buddha_d — Sat, 13 Jan 2018 17:04:33 GMT

Thank you for the suggestion Pgstat. I do have last name that have gaps in last names and that is different when I compare both with proc compare. Is there a way to fix this?

Thanks a lot.

Re: Proc sql case when condition to create multiple variables vs data step

PGStats — Sat, 13 Jan 2018 21:47:30 GMT

Give an example, please.