Translating SAS code to SQL

[BigVinnie]

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

[PeterClemmensen]

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

[BigVinnie]

Thank you for your help

[Kurt_Bremser]

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.

[Tom]

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?