Could someone tell me an equivalent SAS function for the EXCEL function iferror(), which I could use in a PROC SQL variable assignment.
For example, in an datastep if I have a SAS variable defined as:
AGE = YRDIF( birthdate, today(), 'ACTUAL');
but for some reason in my data the birthdate is missing, then I get an error. This does not stop SAS, but I would like it to put, for example, 22 (the average age) if the birthdate is missing. In a datastep I would write:
if missing(birthdate) then AGE = YRDIF( birthdate, today(), 'ACTUAL'); else AGE = 22;
However, in a PROC SQL, how would I do this? All I can think of is if there were an equivalent SAS function like the EXCEL iferror() function, then I could do this in one line. In other words, I would like to write the "equivalent" of (using the EXCEL function):
AGE = iferror( YRDIF( birthdate, today(), 'ACTUAL'), 22);
If the first argument is null or error, then the second argument is returned for AGE.
Thanks in advance.
Ian.
Here is a one line approach:
data have;
birthday='01jan2012'd;
output;
birthday=.;
output;
run;
proc sql;
create table WANT as
select *,
yrdif(COALESCE(BIRTHDAY,'15JUL1992'd),today(),'actual') as AGE
from HAVE;
quit;
So coalesce takes the first non-null value from the list, birthday first, then your default, and caluculates on that value.
I would suggest you try the equivalent to Excel's "isblank" instead of "iserror". Could look like:
Data A;
Input Birthdate:Date9.; Format Birthdate Date9.;
Datalines;
01JAN2000
09SEP2006
.
;
Run;
Proc SQL;
Create Table B As
Select *,
Case When Missing (Birthdate) Then 22 Else
YrDif(Birthdate,Today(),'Actual') End As Age
From A;
Quit;
Thanks very much. I just happened to find almost exactly the same solution after searching some more. Thanks.
Ian.
Here is a one line approach:
data have;
birthday='01jan2012'd;
output;
birthday=.;
output;
run;
proc sql;
create table WANT as
select *,
yrdif(COALESCE(BIRTHDAY,'15JUL1992'd),today(),'actual') as AGE
from HAVE;
quit;
So coalesce takes the first non-null value from the list, birthday first, then your default, and caluculates on that value.
thanks a lot!
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 16. Read more here about why you should contribute and what is in it for you!