SAS vs SQL

[latristain1]

Hello,

How can a replicate the do statement in sql

For example:

data x;

set y;

if x = 1 then do;

a = 1

b=1

c=1

end;

run;

I dont know how to do this with a case statement or if there ir another way

proc sql;

case when x = 1 then 1 end as a;

case when x = 1 then 1 end as b;

case when x = 1 then 1 end as c;

from y

IS THERE ANOHER WAY TO DO IT JUST ENTERING ONE CONDITION LIKE IN A DATA STEP.

Regards for your answers

[PaigeMiller]

I would say that in this case, use the DATA step, don't use SQL. Yes, I know that doesn't really answer your question, but: Use the right tool. Maxim 14.

Please use semi-colons properly when presenting SAS code.

[ballardw]

Case statements would be part of a Select clause and would be separated by commas. To be at all useful likely need to create a new data set

proc sql;
   create table want as
   select <other variables go here separated by commas>
   case when x = 1 then 1 end as a,
   case when x = 1 then 1 end as b,
   case when x = 1 then 1 end as c   
   from y
   ;
quit;

SQL syntax sometimes just plain requires more text in the code. Such as you need to specify which variables that come from the data set you want in the output, either with an * (all variables) or list each one by name.

Note your data step approach is missing a bunch of semicolons.

[Reeza]

Your SQL is close, you forgot the SELECT * and have semicolons instead of commas. 

proc sql;

select *,
case when x = 1 then 1 end as a,
case when x = 1 then 1 end as b,
case when x = 1 then 1 end as c

from y;
quit;

---

@latristain1 wrote:

Hello,

How can a replicate the do statement in sql

For example:

data x;

set y;

if x = 1 then do;

a = 1

b=1

c=1

end;

run;

I dont know how to do this with a case statement or if there ir another way

proc sql;

case when x = 1 then 1 end as a;

case when x = 1 then 1 end as b;

case when x = 1 then 1 end as c;

from y

IS THERE ANOHER WAY TO DO IT JUST ENTERING ONE CONDITION LIKE IN A DATA STEP.

Regards for your answers

---

[latristain1]

Thanks but is there a way like SAS to put only one do end statement with conditions instead of various case when I have to enter like 50 different conditions and is waste of tyme typing 50 time case when x = 1 case when x = 1 case when x = 1 ...... case when x = 1 50 times . Regards for your replies.

[ballardw]

---

@latristain1 wrote:
Thanks but is there a way like SAS to put only one do end statement with conditions instead of various case when I have to enter like 50 different conditions and is waste of tyme typing 50 time case when x = 1 case when x = 1 case when x = 1 ...... case when x = 1 50 times . Regards for your replies.

---

Not in Proc SQL. Just not designed that way.

And perhaps your data step could use arrays to make the code more compact as well.

You seem to have some objection to using the data step. What is it?

[Tom]

Why are you setting so many variables to the same value?

What do you want to do when the condition is not true?

Why do want to use SQL?

If you are willing to make new variables that are coded as 0 or 1 instead of 1 or unknown like your current code you could take advantage of the fact that SAS evaluates boolean expressions to 1 for TRUE and 0 for FALSE.

proc sql;
create table x as
  select *
       , (X=1) as a
       , (X=1) as b
       , (X=1) as c
  from y
;
quit;