BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
wwwz
Calcite | Level 5


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

1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20

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

 

View solution in original post

4 REPLIES 4
novinosrin
Tourmaline | Level 20

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

 

wwwz
Calcite | Level 5
thanks
ballardw
Super User

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

 

wwwz
Calcite | Level 5

thanks

sas-innovate-2024.png

 

Time is running out to save with the early bird rate. Register by Friday, March 1 for just $695 - $100 off the standard rate.

 

Check out the agenda and get ready for a jam-packed event featuring workshops, super demos, breakout sessions, roundtables, inspiring keynotes and incredible networking events. 

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 1164 views
  • 0 likes
  • 3 in conversation