turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-24-2014 03:01 AM

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.

Accepted Solutions

Solution

11-24-2014
04:04 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to iank131

11-24-2014 04:04 AM

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.

All Replies

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to iank131

11-24-2014 03:32 AM

I would suggest you try the equivalent to Excel's "isblank" instead of "iserror". Could look like:

Data A;

Input Birthdateate9.; 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;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to user24feb

11-24-2014 03:40 AM

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

Ian.

Solution

11-24-2014
04:04 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to iank131

11-24-2014 04:04 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-24-2014 05:34 AM

thanks a lot!