topic Re: 2 Tips related to COMMAS and CASE expressions in PROC SQL in SAS Programming

2 Tips related to COMMAS and CASE expressions in PROC SQL

jjsingh04 — Wed, 03 Jun 2020 21:10:59 GMT

Hi everyone. This isn't a question but a couple of tips.

In PROC SQL, remember that:

1) a) If you use a CASE expression, you MUST put a COMMA at the end of the SELECT statement that PRECEDES the CASE expression.

b) However, if you DON'T use a CASE expression, you MUST NOT put a COMMA at the end of your SELECT statement.

2) a) When a CASE expression is followed by another CASE expression, you MUST put a COMMA at the end of the 1st CASE expression.
b) But if a CASE expression is NOT followed by another CASE expression, you MUST NOT put a COMMA at the end of it.

See the code below for examples.

proc sql; 
create table coloid as   
select colic.* , 
		case 
			when IND=1 then 1
			else 0
		end as i1,
		case 
			when IND=2 then 1
			else 0
		end as i2
from colic
where LEV<=0.999
order by colic.cusip, colic.fyear;
quit;

Re: 2 Tips related to COMMAS and CASE statements in PROC SQL

Patrick — Tue, 02 Jun 2020 23:19:59 GMT

@jjsingh04

?? Your statements don't make sense to me.

You just have a comma separated list in the Select statement where an element can be just a column name or an expression. There is nothing special about the CASE statement.

Re: 2 Tips related to COMMAS and CASE expressions in PROC SQL

jjsingh04 — Wed, 03 Jun 2020 21:13:17 GMT

Patrick,

What I mean is, if you forget to put a comma in where you should, you will get an error message, and if you put in an extra comma in where you shouldn't, you will get an error message. In either case, your code won't run. Consider the following 3 examples that would lead to errors in the code below:

1) missing the comma after select colic.*

2) missing the comma after end as i1

3) having an extra (unwarranted) comma after end as i2

proc sql; 
create table coloid as   
select colic.* , 
		case 
			when IND=1 then 1
			else 0
		end as i1,
		case 
			when IND=2 then 1
			else 0
		end as i2
from colic
where LEV<=0.999
order by colic.cusip, colic.fyear;
quit;

Re: 2 Tips related to COMMAS and CASE statements in PROC SQL

ChrisNZ — Wed, 03 Jun 2020 02:46:35 GMT

There should be a comma between each element listed after the select keyword, used as a separator.

Whether this element is derived from a variable name, a calculation, a function, a case statement, or a combination of these, makes no difference.

The rule is simply: There must a comma between each query element listed after the select keyword, to separate them. Not before. Not after. Between.

I fail to see why you consider case to be special.

Re: 2 Tips related to COMMAS and CASE expressions in PROC SQL

jjsingh04 — Wed, 03 Jun 2020 21:12:18 GMT

Chris,

I take it from what you and Patrick have written that there are some generalities for SELECT's syntax that I'm not as familiar with. I'm just trying to help people who are new to using the CASE expression in overcoming the coding problems with commas that I've had to deal with in the past. I'm not trying to imply that CASE expressions are necessarily unique in their construct; I've just found them to be particularly problematic for new coders.

J.J.

Re: 2 Tips related to COMMAS and CASE statements in PROC SQL

s_lassen — Wed, 03 Jun 2020 05:37:47 GMT

The CASE thing is not a "statement", it is an SQL element, and is part of your SELECT statement, which has a list of comma-separated columns (with "*" denoting all columns from the input).

Re: 2 Tips related to COMMAS and CASE expressions in PROC SQL

jjsingh04 — Wed, 03 Jun 2020 22:02:39 GMT

That's interesting. Thanks for the info.

J.J.