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

Question

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;

Could you just add counts in a group; then select the combination of the Value and its count? (For the first part, see here: http://stackoverflow.com/questions/17006765/get-a-the-row-number-in-a-data-step-using-sas) — johnjps111, Nov 22 '16 at 19:21
Or you could do this within a data step since the SQL doesn't have anything that a data step couldn't easily replicate. — Reeza, Nov 22 '16 at 22:25

score 3 · Answer 1 · answered Nov 23 '16 at 09:54

3

Just for completeness, this is straightforward in a data step, using select, when, do, otherwise. Or you could just use if, then, do, else.

data want;
set example;
select (x);
    when ('A') do;
        thing1 = 'Result1A';
        thing2 = 'Result2A';
        end;
    when ('B') do;
        thing1 = 'Result1B';
        thing2 = 'Result2B';
        end;
    otherwise do;
        thing1 = 'Error';
        thing2 = 'Error';
        end;
end;
run;

answered Nov 23 '16 at 09:54

Longfish

7,582
13
19

The other answers offer more general solutions, but this is the best answer for the specific original question. I.e., if you have a linear traversal of a single table with no merge, nothing beats the data step. This is also the most clear and elegant code when you need to add several columns based on the value of an existing column. – Leo Nov 23 '16 at 16:47

score 1 · Accepted Answer · answered Nov 22 '16 at 19:09

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;

score 0 · Answer 3 · answered Nov 22 '16 at 19:37

In your case, maybe you could try this:

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,
    translate(calculated thing1,'2','1') as thing2
   from example
  ;
quit;

Leo · Answer 4 · 2016-11-23T00:51:41.513

Personally I think this need, i.e., to overcome a cumbersome syntactic barrier with something that can be maintained in one place, is a good use case for SAS macro.

The macro version also avoids the perils of parsing, is nicely tabular, and makes no type assumptions.

%macro _case(when_A, when_B, error='Error');
      case
          when x = 'A' then &when_A
          when x = 'B' then &when_B
          else &error
      end
%mend;

proc sql;
  create table make_two_from_one_v2 as
  select *
  , %_case('Result1A', 'Result1B') as thing1
  , %_case('Result2A', 'Result2B') as thing2
  from example
  ;
quit;

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

4 Answers4