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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.