Using PROC SQL as an IF/ELSE IF statement

Question

I'm trying to run the following code but it not working properly. I figured out that the problem is that each case when overwrites the next statement.

So, what I need to do is a IF/ELSE IF staetment but I don't know how to do that in PROC-SQL

proc sql;
create table example
as select *,

case when B.variable = 'X'               then 1 else 0 end as variable_X,   
case when B.variable = 'Y'               then 1 else 0 end as variable_Y,   
case when B.variable = 'Z'               then 1 else 0 end as variable_Z,   
case when B.variable = 'W'               then 1 else 0 end as variable_W,   
case when B.variable = 'R'               then 1 else 0 end as variable_R,
case when B.variable = 'G'               then 1 else 0 end as variable_G,
case when B.variable = 'T'               then 1 else 0 end as variable_T,   
case when B.variable = 'U'               then 1 else 0 end as variable_U,
case when B.variable = 'P'               then 1 else 0 end as variable_P,   
case when B.variable = 'L'               then 1 else 0 end as variable_L

FROM my_table             as A
LEFT JOIN my_second_table as B
on A.KEY1=E.KEY1  and A.KEY2=E.KEY2
;

I've already tried to use a group by statement but it didn't work.

P.S.: My real code is much greater than my example, with 8 left join and much more variables. I just posted an extract of it.

Your comment about "overwriting" doesn't make sense. You can certainly do (more or less) what you're doing above and get a potentially valid result. — Joe, Nov 08 '16 at 17:53

score 1 · Answer 1 · answered Nov 08 '16 at 17:56

In SAS, if you're trying to do what you're doing there, you shouldn't do it with proc sql. You should do it either in a data step or proc transpose.

If I had SASHELP.CLASS and wanted every age as a flag, I could do:

proc sql;
  select name, age,
    case when age=11 then 1 else 0 end as age_11,
    case when age=12 then 1 else 0 end as age_12
    from sashelp.class;
quit;

Etc. - lot of code, and you hardcoded the possible values. Or:

data class;
  set sashelp.class;
  x=1;
run;

proc transpose data=class out=class_t prefix=age_; 
  by name;
  id age;
  var x;
run;

Then merge that back on however you wish assuming you have other data that's useful. You may already have a variable you could pop in for the placeholder x rather than making one on the fly.

Using PROC SQL as an IF/ELSE IF statement

1 Answers1