SAS to SQL

[HeatherNewton]

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

[Reeza]

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. 

[HeatherNewton]

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

[Patrick]

---

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

[Reeza]

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.

[SASKiwi]

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.

[Patrick]

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