DATA Step, Macro, Functions and more

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

Reply
Frequent Contributor
Posts: 112

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

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;

Highlighted
Esteemed Advisor
Posts: 5,403

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

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

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

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.

Esteemed Advisor
Posts: 5,403

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

Give an example, please.

PG
Ask a Question
Discussion stats
  • 3 replies
  • 663 views
  • 1 like
  • 2 in conversation