PROC SQL vs. T-SQL

[DataScientist]

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.

[Reeza]

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

Biggest difference - look into Windowing function 

[DataScientist]

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

[Reeza]

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

[Reeza]

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

[LinusH · Accepted solution]

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.

[DataScientist]

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.

[DataScientist]

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.