Need help with nested if statement

[kajal_30]

Hi There, 

I have a code which I am trying to convert from Data step to proc sql 

sample code 

if a = 0 and b = 1 and s= " " then do ;

r = 1 ;

s= 2;

t = 3;

        if p = 2 then do;

            q= 1;

             r= 3 ;

        end ;

       else do ;

      q= 0;

      r = 0;

      end ;

end ;

  how can we convert this into case when ?

Thanks 

[andreas_lds]

Why? Proc SQL won't perform better.

[kajal_30]

Its a business requirement to keep the code in sync  as all other processes are using the sql 

[Kurt_Bremser]

---

@kajal_30 wrote:

Its a business requirement to keep the code in sync  as all other processes are using the sql 

---

That's an incredibly stupid argument. You use the tools best suited for a job, period. The one that gave you this "requirement" is a clueless noob.

Not using data steps when using SAS is like trying to drive a Ferrari with coal.

[mkeintz]

The argument, however stupid, is probably based on the expectation that the business is more likely to find programmers that understand sql than the sas data step.

Of course, while this may initially seem to be a reasonable notion (I'm not ready to elevate to idea status), it counters one of the best reasons for getting a sas license to begin with.

Or perhaps it may seem like a good condition to set in order to later movere to some software environment other than SAS.

[s_lassen]

SQL case-when statements have the limitation that you can only assign to one variable in each statement, so it becomes a bit tedious:

select 
case
  when a = 0 and b = 1 and s= " " then case
    when p=2 then 3
    else 0
    end
  else null
end as r,
case
  when a = 0 and b = 1 and s= " " then case
    when p=2 then 1
    else 0
    end
  else null
end as q,
case
  when a = 0 and b = 1 and s= " " then 2
  else null
end as s,
case
  when a = 0 and b = 1 and s= " " then 3
  else null
end as t

There was a small glitch in your original code: First you assign the value 1 to R, then you make it 3 if P=2 and else 0. So the original assignment to 1 is never used for anything, which is why you won't see it in the code above.

[kajal_30]

sorry here's my code
if a = "s" and m = "" and f = . then do ;
x = c ;
e = l;
n = 0 ;
if rs = 0 then do ;
j= 1;
k= 2;
w = 3 ;
end ;
else if o ne p then do ;
j=3;
k=4;
w = 3 ;
else do ;
j=0;
k=0;
d = 6;
r = 7 ;
end;
end;

[Kurt_Bremser]

Since you set 13 variables, you would need 13 case/when clauses in your SQL select. Stay with the data step.

[FreelanceReinh]

Hi @kajal_30,

You could also abbreviate repetitive code to obtain a more condensed PROC SQL step:

%let cond=case when a=0 & b=1 & s=" " then 1 else . end;

proc sql;
create table want as
select
  &cond*(p=2)   as q,
  &cond*(p=2)*3 as r,
  &cond*2       as s,
  &cond*3       as t
from have;
quit;