topic Re: PROC SQL vs. T-SQL in SAS Programming

PROC SQL vs. T-SQL

DataScientist — Sat, 06 Aug 2016 06:16:55 GMT

Hi, in an upcoming engagement I will need to use T-SQL instead of SAS and PROC SQL. Has anyone used T-SQL before? If so, are there any important differences in syntax that might be of value to know beforehand. Also, is there a resource that I can refer to that might show me how a certain query might be written differently in T-SQL compared to PROC SQL.

The T-SQL will be used primarily for querying and reporting purposes rather than database administration purposes.

Re: PROC SQL vs. T-SQL

Reeza — Sat, 06 Aug 2016 06:36:47 GMT

T-SQL is Microsoft so there's tons of documentation and examples out on the web.

Biggest difference - look into Windowing function

Re: PROC SQL vs. T-SQL

DataScientist — Sat, 06 Aug 2016 06:42:52 GMT

Thanks @Reeza. Will look into the difference in the Windowing function.

Re: PROC SQL vs. T-SQL

Reeza — Sat, 06 Aug 2016 06:53:32 GMT

It's not so much difference as something SAS doesn't have.

Re: PROC SQL vs. T-SQL

Reeza — Sat, 06 Aug 2016 06:54:50 GMT

And Pivot? Or is that Oracle? Look into how they do processing over groups basically, besides group by.

Re: PROC SQL vs. T-SQL

LinusH — Sat, 06 Aug 2016 17:34:53 GMT

Except from the obvious SAS specifics like functions and formats, there two differences I think is worth mentioning :
- no support for the "remerging summary statics with original data". You need the spicy join the aggregation results back.
- no support for the calculated key word. You need to repeat any formula wich result you wish to use more than once in the same query.

Re: PROC SQL vs. T-SQL

DataScientist — Thu, 11 Aug 2016 16:49:28 GMT

Thank you for your input @LinusH. This is great I'm sure I will learn more along the way. One difference I chanced upon recently was with the AS keyword while assigning Aliases.

The AS keyword is recommended but can be omitted while assigning an Alias in T-SQL.

Not having the AS keyword while assigning an Alias in PROC SQL causes an error.

Re: PROC SQL vs. T-SQL

DataScientist — Sat, 13 Aug 2016 12:17:52 GMT

Another minor difference is while eliminating duplicate rows:

The DISTINCT keyword is common between T-SQL and PROC SQL. PROC SQL also has an alternative, the UNIQUE keyword. This, I believe, isn't available in T-SQL.