topic Re: Convert MS Access iif to macro %iif in SAS Programming

Convert MS Access iif to macro %iif

tomrvincent — Thu, 10 Oct 2019 13:02:52 GMT

Does anyone know a way to convert the MS Access iif function to a case statement *in bulk*?

I'm converting Access apps to SAS and found one with dozens of iif statements and I thought it would be 'fun' (sick, yes, I know) to try to come up with a way to take this:

IIf(MEPE_ELIG_IND="Y",MEME_LAST_NAME,Null)

and write a macro so that I could simply do this:

%IIf(MEPE_ELIG_IND="Y",MEME_LAST_NAME,Null)

that would convert that to

case when MEPE_ELIG_IND="Y" then MEME_LAST_NAME else Null end

I thought I'd be clever and just come up with something like this:

%macro iif(condition,true,else);
case when &condition then &true else &else end
%mend iif;

But, of course, SAS considers MEPE_ELIG_IND="Y" to be passing a value to the macro variable MEPE_ELIG_IND instead of a whole value for variable &condition.

Just wondered if anybody had ideas for this.

Re: Convert MS Access iif to macro %iif

Tom — Thu, 10 Oct 2019 13:11:54 GMT

Either include the parameter name in the call:

%IIf(CONDITION=MEPE_ELIG_IND="Y",MEME_LAST_NAME,Null)

Or add parentheses around the condition.

%IIf((MEPE_ELIG_IND="Y"),MEME_LAST_NAME,Null)

Or both.

Re: Convert MS Access iif to macro %iif

Tom — Thu, 10 Oct 2019 13:15:22 GMT

Why not just use the IFN() or IFC() functions instead of making a macro?

Re: Convert MS Access iif to macro %iif

Kurt_Bremser — Thu, 10 Oct 2019 13:22:10 GMT

Since

%IIf(MEPE_ELIG_IND="Y",MEME_LAST_NAME,Null)

takes longer to write than

ifc(MEPE_ELIG_IND="Y",MEME_LAST_NAME,"")

I really see no value in creating a macro.

Re: Convert MS Access iif to macro %iif

tomrvincent — Thu, 10 Oct 2019 15:03:44 GMT

Doh! That did the trick! Thanks so very much!

Re: Convert MS Access iif to macro %iif

tomrvincent — Thu, 10 Oct 2019 17:41:15 GMT

And if I'd remembered IFC I wouldn't have posted the question. No value indeed.