topic Re: Need help with nested if statement in SAS Programming

Need help with nested if statement

kajal_30 — Tue, 05 Feb 2019 05:42:21 GMT

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 ;

how can we convert this into case when ?

Thanks

Re: Need help with nested if statement

andreas_lds — Tue, 05 Feb 2019 06:27:02 GMT

Why? Proc SQL won't perform better.

Re: Need help with nested if statement

kajal_30 — Tue, 05 Feb 2019 06:34:38 GMT

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

Re: Need help with nested if statement

s_lassen — Tue, 05 Feb 2019 08:22:20 GMT

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.

Re: Need help with nested if statement

Kurt_Bremser — Tue, 05 Feb 2019 09:56:51 GMT

@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.

Re: Need help with nested if statement

FreelanceReinh — Tue, 05 Feb 2019 11:04:23 GMT

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;

Re: Need help with nested if statement

kajal_30 — Wed, 06 Feb 2019 18:08:10 GMT

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;

Re: Need help with nested if statement

Kurt_Bremser — Wed, 06 Feb 2019 19:06:52 GMT

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

Re: Need help with nested if statement

mkeintz — Thu, 07 Feb 2019 01:53:12 GMT

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.