BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
iank131
Quartz | Level 8

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.

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

View solution in original post

4 REPLIES 4
user24feb
Barite | Level 11

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;

iank131
Quartz | Level 8

Thanks very much. I just happened to find almost exactly the same solution after searching some more. Thanks.

Ian.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

iank131
Quartz | Level 8

thanks a lot!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

Discussion stats
  • 4 replies
  • 11500 views
  • 4 likes
  • 3 in conversation