CODEWITHSUNDEEP
if-statementsasproc-sql
Rods2292
Rods2292

Reputation: 675

Using PROC SQL as an IF/ELSE IF statement

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.

Upvotes: 0

Views: 24906

Answers (1)

Joe
Joe

Reputation: 63424

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.

Upvotes: 1

Related Questions