topic Re: need help for nested case when. in SAS Procedures

need help for nested case when.

wwwz — Wed, 04 Mar 2020 18:07:37 GMT

proc sql;
create table check1 as
select *,

case when new=1 then
case when aa=1 then 1 else 0 end as aa1,
case when aa=2 then 1 else 0 end as aa2,
case when aa=3 then 1 else 0 end as aa3

end

from id;
quit;

I wrote the code above, it looks not right.

The question is how I close the big loop for new=1?

thanks

Re: need help for nested case when.

novinosrin — Wed, 04 Mar 2020 18:15:06 GMT

Hi @wwwz Not quite suited for "nested" case when, rather it's far more convenient and precise to use AND operator like

case when new=1 and aa=1 then 1 else 0 end as aa1,
case when new=1 and aa=2 then 1 else 0 end as aa2,
case when new=1 and aa=3 then 1 else 0 end as aa3

Re: need help for nested case when.

ballardw — Wed, 04 Mar 2020 18:35:44 GMT

@wwwz wrote:

proc sql;
create table check1 as
select *,

case when new=1 then
case when aa=1 then 1 else 0 end as aa1,
case when aa=2 then 1 else 0 end as aa2,
case when aa=3 then 1 else 0 end as aa3

end

from id;
quit;

I wrote the code above, it looks not right.

The question is how I close the big loop for new=1?

thanks

Might say this is more of a data step task:

data check1;
  set id;
  if new=1 then do;
   aa1 = (aa=1);
   aa2 = (aa=2);
   aa3 = (aa=3);
  end;
run;

Especially if you have may more values to check. Consider if AA takes values of 1 to 25. That gets to be a lot of CASE coding.

But with an array in a data step:

data work.id;
   input new aa;
datalines;
1 1
1 3
0 1
1 25
;

data work.check1;
   set work.id;
   array a(25) aa1-aa25;
   if new= 1 then do i= 1 to dim(a);
      a[i]= (aa=i);
   end;
   drop i;
run;

And ARRAYS do not work in SQL.

Re: need help for nested case when.

wwwz — Wed, 04 Mar 2020 19:01:47 GMT

thanks

Re: need help for nested case when.

wwwz — Wed, 04 Mar 2020 19:02:03 GMT

thanks