CODEWITHSUNDEEP
sas
Adjoe
Adjoe

Reputation: 1

Using IIF in Proc SQL

I want to translate the following snippet of code for use in Proc SQL for SAS:

SUM( IIF( INLIST( a.Department, DptGI, DptOncology, DptSurgery ), 1, 0 )) as TotalApts,;

However IIF() is not recognized by PROC SQL.

Can I implement an if/else or some kind of CASE statement?

None of that seems to be working for me.

Upvotes: 0

Views: 672

Answers (2)

Tom
Tom

Reputation: 51611

IIF() is not an SQL function. It might be a function that is implemented as an extension in some version of SQL, most likely those created by Microsoft.

I am not sure what INLIST() is either. I will assume it is some type of test of whether the first argument is in the other arguments. If so you could replace it with one of the SAS functions WHICHN() or WHICHC(), depending on whether the values in the variables are numbers or strings.

The normal SQL syntax for that type of operation is CASE. 

SUM( case when ( a.Department= DptGI) then 1
          when ( a.Department=DptOncology) then 1
          when ( a.Department=DptSurgery ) then 1
      else 0 end
    ) as TotalApts

If you are just trying to get result of 1 or 0 and you are ok with using SAS specific syntax then you can just sum the result of the boolean expression.

SUM( (a.Department=DptGI) or (a.Department=DptOncology)
     or (a.Department=DptSurgery)) as TotalApts

Now if DPTGI and the other values are constants and not variables then you could use the IN operator.

SUM(a.Department in ('GI','Oncology','Surgery')) as TotalApts

Upvotes: 1

Joe
Joe

Reputation: 63424

The SAS version of IIF is IFN or IFC, depending on numeric or character return value (doesn't matter what the input columns are, just what you want back). As long as you're using it in native SAS PROC SQL (and not pass-through SQL) this should work.

However, INLIST is also not a SAS function, you'd have to rewrite that to a normal in operator as well.

You should be able to use case when statements as well, however, if you would like help with those you can post your code that you've tried.

Upvotes: 1

Related Questions