Lorem Ipsum Lorem Ipsum - 16 days ago 5
SQL Question

SAS: PROC SQL Multiple assignments as a consequence of a single CASE

I would like to know if it is possible to execute multiple assignments as a consequence of a single CASE. That is, instead of having two CASE statements, have a single CASE statement with a 'then-do-end' like structure.

For example, how would I assign values to

thing1
and
thing2
based on
x
within a single CASE statement?

data example;
input x $;

datalines;
A
A
A
B
B
;
run;

proc sql;
create table make_two_from_one as
select *
, case
when x = 'A' then 'Result1A'
when x = 'B' then 'Result1B'
else 'Error'
end as thing1
, case
when x = 'A' then 'Result2A'
when x = 'B' then 'Result2B'
else 'Error'
end as thing2
from example
;
quit;

Answer

Case statement constructs one variable.

For your example you can try this, with one statement of Case :

data example;
  input x $;

  datalines;
  A
  A
  A
  B
  B
  ; 
run;

proc sql;
  create table make_two_from_one_2 as
  select *
  , case
      when x = 'A' then 'Result1A,Result2A'
      when x = 'B' then 'Result1B,Result2B'
      else 'Error'
    end as thing0

  from example
  ;
quit; 

data example1(drop=thing0);
  set make_two_from_one_2;
  thing1=scan(thing0,1,',');
  thing2=scan(thing0,2,',');
run;