Help using Base SAS procedures

How do you layout your PROC SQL code?

Reply
Contributor jp
Contributor
Posts: 29

How do you layout your PROC SQL code?

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;

Valued Guide
Posts: 2,175

Re: How do you layout your PROC SQL code?

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

PROC Star
Posts: 7,366

Re: How do you layout your PROC SQL code?

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.

Valued Guide
Posts: 2,175

Re: How do you layout your PROC SQL code?

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.

PROC Star
Posts: 7,366

Re: How do you layout your PROC SQL code?

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

Valued Guide
Posts: 2,175

Re: How do you layout your PROC SQL code?

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?

Respected Advisor
Posts: 4,663

Re: How do you layout your PROC SQL code?

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
Respected Advisor
Posts: 3,124

Re: How do you layout your PROC SQL code?

"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

Respected Advisor
Posts: 3,908

Re: How do you layout your PROC SQL code?

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

Frequent Contributor
Posts: 110

Re: How do you layout your PROC SQL code?

Ask a Question
Discussion stats
  • 9 replies
  • 437 views
  • 0 likes
  • 7 in conversation