topic Re: SAS to SQL in SAS Programming

SAS to SQL

HeatherNewton — Tue, 10 May 2022 01:36:50 GMT

Hi I am working on a large project where we want to migrate data from SAS to DataStage and need to convert SAS code to SQL for ease of reference. My question is:

How to write SQL to replace MERGE for cases below:
Case 1:
Table A, no of var is M, no of row is N
Table B, no of var is O, no of rows is P
Merge A, B By 1 variable

Case 2
Table A, no of var is M, no of rows is N
Table B, no of var is O, no of rows is P
Merge A, B By 2 variable

Where M, N, O, P can be any numbers

I find this very difficult please kindly advise
Your help would be much appreciated

Re: SAS to SQL

Reeza — Tue, 10 May 2022 01:52:12 GMT

If they're just merges it's pretty straightforward.

If there's functions in the SQL as well (data transformation) or other functions it can get more complicated.

This book is a good reference:

https://support.sas.com/en/books/authors/kirk-paul-lafler.html

Or try W3 school:

https://www.w3schools.com/sql/

Note that not all data step merges can be replicated in SQL, many to many merges in a data step will not align with a SQL merge for example.

Re: SAS to SQL

Patrick — Tue, 10 May 2022 03:52:40 GMT

Here a SAS Docu link that might be useful to you.

Re: SAS to SQL

HeatherNewton — Tue, 10 May 2022 07:53:13 GMT

Does that mean I will have to write in SQL from sketch the logic of SAS merge on a case by case basis?

Re: SAS to SQL

Patrick — Tue, 10 May 2022 09:10:45 GMT

@HeatherNewton wrote:

Does that mean I will have to write in SQL from sketch the logic of SAS merge on a case by case basis?

Yes!

And some of the additional sequential logic normally also being part of a SAS data step will be hard to translate into SQL / will require some PL/SQL or similar depending on the database you want to run your code.

Re: SAS to SQL

Reeza — Tue, 10 May 2022 15:19:21 GMT

It depends on the complexity of your queries. If they're merges without anything it's pretty straightforward.

But other data step logic, like FIRST/LAST/BY group processing can be difficult to replicate in SQL. Lag is also typically a pain.

Re: SAS to SQL

SASKiwi — Tue, 10 May 2022 19:51:20 GMT

Typically DataStage uses other relational databases for data storage. Which one will you be using? You will need to write SQL compatible with that database.