topic Re: Translating SAS code to SQL in New SAS User

Translating SAS code to SQL

BigVinnie — Sat, 26 Jan 2019 08:32:02 GMT

Hello Everybody,

I am completely new to the world of SAS and I need to translate a SAS case stateemnt to SQL.

here is the line of code

WHEN (INDEX(UPCASE(SALES_STATUS), 'FORM RECEIVED')) >= 1 THEN 'Form Received'

a) In English what is this line of code doing?

b) how would I write this in SQL?

I can't seem to get the functions or syntax to work in SQL.

Any help would be much appreciated.

Kind Regards

Vinnie

Re: Translating SAS code to SQL

PeterClemmensen — Sat, 26 Jan 2019 14:36:10 GMT

Welcome to the SAS communities. The questions are better answered reversed:

b) The code in PROC SQL goes something like this

proc sql;
   create table want as
   select (case 
             when (index(upcase(sales_status), 'FORM RECEIVED')) >= 1 then 'Form Received' 
           end) as newvar
   from have;
quit;

a) The code looks for the string "FORM RECEIVED" (not considering case) in the variable sales_status. If the string is present, the variable newvar will have the value "Form Received".

Re: Translating SAS code to SQL

Kurt_Bremser — Sat, 26 Jan 2019 14:44:20 GMT

If you need to translate this to a non-SAS SQL system, you will probably have to replace index() and upcase() with functions available there. The rest of the code is standard SQL and should need to adaptation.

Re: Translating SAS code to SQL

Tom — Sat, 26 Jan 2019 15:11:30 GMT

Can you show more of the full code that you are trying to translate?

I am confused because the snippet that you did show looks like it is part of an SQL CASE expression.

Why would you need to translate SQL to SQL?

Re: Translating SAS code to SQL

BigVinnie — Sat, 26 Jan 2019 21:25:16 GMT

Thank you for your help