topic Re: How do you layout your PROC SQL code? in SAS Procedures

How do you layout your PROC SQL code?

jp — Mon, 13 Aug 2012 21:47:36 GMT

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;

Re: How do you layout your PROC SQL code?

Peter_C — Mon, 13 Aug 2012 21:51:47 GMT

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

Re: How do you layout your PROC SQL code?

PGStats — Mon, 13 Aug 2012 22:11:48 GMT

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:

Re: How do you layout your PROC SQL code?

art297 — Mon, 13 Aug 2012 22:18:23 GMT

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.

Re: How do you layout your PROC SQL code?

Peter_C — Tue, 14 Aug 2012 21:34:47 GMT

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.

Re: How do you layout your PROC SQL code?

art297 — Tue, 14 Aug 2012 21:49:57 GMT

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

Re: How do you layout your PROC SQL code?

Peter_C — Tue, 14 Aug 2012 21:58:45 GMT

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?

Re: How do you layout your PROC SQL code?

Haikuo — Tue, 14 Aug 2012 22:00:22 GMT

"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

Re: How do you layout your PROC SQL code?

sassharp — Wed, 15 Aug 2012 03:25:26 GMT

The last but not the least.

http://www2.sas.com/proceedings/sugi29/258-29.pdf

http://www2.sas.com/proceedings/sugi31/123-31.pdf

http://www.nesug.org/Proceedings/nesug09/po/po02.pdf

Re: How do you layout your PROC SQL code?

Patrick — Wed, 15 Aug 2012 11:04:13 GMT

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