Hello,
I've been searching through google (and SAS discussions) for optimal and efficient lay out for SQL code, so that it is easy to read and debug.
Here's how I would generally layout my code, but I'm really interested in seeing your layout!
PROC SQL;
create table foo as
select
a.column_1,
b.column_2,
CASE
WHEN b.column_3 = "Something" THEN 1
WHEN b.column_3 = "Something else" THEN 2
ELSE 3
END as new_column_3
from
table_1 as a
left join
table_2 as b
on a.primary_key = b.primary_key
and a.secondary_key = b.secondary_key
where a.column_1 = "Value"
and b.column_2 = "Value"
;QUIT;
not just semi-colons - place commas at the beginning of a line
Peter, Can you explain your comment?
The only things I would have suggested are (1) indenting each section more so that select is indented more than create, create indented more than from, etc. and (2) putting the final semicolon on a separate line .. indented the same amount at the create statement. Although, even those, are just my own preferences and, as we all know, I am not really a programmer.
Art
I saw a ; before the quit.
but perhaps that was an abberation
For part of my sql syntax style, I have adopted the style offered by the DESCRIBE VIEW layout.
Another part of the syntax style relates to that issue of the semicolon - I never place the comma at the end of the line - only at the beginning, when making the list of columns selected vertical
select a
, b
, c
from somewhere
;
A better example might have helped.
The style ensures you see the commas more easily, and can easily move lines around.
Again, the semicolon is placed where it stands out.
Peter: thanks! And, I agree, it is quite easy to read that way.
by the way, the sheer convenience of the CTRL+i in EG4.3+ "might" persuade me to adopt that layout, but it's just not good enough, yet.
When do you think we might get a few more controls on that layout style?
I wish I had the patience to be that neat!
And then, when I paste to this forum my wonderful indentation gets all squashed. :smileyangry:
PG
"And then, when I paste to this forum my wonderful indentation gets all squashed."
PG, this requires more patience as well: one more step will get you there: SAS edit copy-paste to MS-word copy-paste to Here, instead of direct copy-paste to here from SAS editor.
Haikuo
"And then, when I paste to this forum my wonderful indentation gets all squashed."
I'm using blanks for indention and when I'm posting code to this forum I always first copy the code from the SAS editor to Notepad++. This way I keep all my indentions.
I believe another way would be to paste into the advanced editor. Just look at the OP's post.
I'm using the same layout style like Peter.C - at least for my more serious professional code. Here in this forum I'm sometimes too lazy for it.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.