BookmarkSubscribeRSS Feed
jp
Fluorite | Level 6 jp
Fluorite | Level 6

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;

9 REPLIES 9
Peter_C
Rhodochrosite | Level 12

not just semi-colons - place commas at the beginning of a line

art297
Opal | Level 21

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.

Peter_C
Rhodochrosite | Level 12

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.

art297
Opal | Level 21

Peter: thanks!  And, I agree, it is quite easy to read that way.

Peter_C
Rhodochrosite | Level 12

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?

PGStats
Opal | Level 21

I wish I had the patience to be that neat! Smiley Happy


And then, when I paste to this forum my wonderful indentation gets all squashed. :smileyangry:

PG

PG
Haikuo
Onyx | Level 15

"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. Smiley Wink

Haikuo

Patrick
Opal | Level 21

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 9 replies
  • 2071 views
  • 0 likes
  • 7 in conversation