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.
Why not just use the IFN() or IFC() functions instead of making a macro?
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.
Why not just use the IFN() or IFC() functions instead of making a macro?
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.