Performance custom functions using proc fcmp

[Owen]

Im trying to replace case statements with custom created proc fcmp functions within a proc sql statement. I've created the functions for translating

character strings (db2 timestamps) into SAS datetime values. These functions do their job, but they take alot more cpu time compaired to the case

statements. With 100k records it takes 70% more time, with 1m records its even worse: 128% more time.

The reason for wanting to use functions in the first place is that we hoped to get some performace increasement. Besides that, they take alot less typing

and are easier to maintain.

I hope to get some answers on the following questions:
- Can I optimize my custom functions so that they will perform better?
- Are functions faster, as fast as or slower compaired to case statements?

Below are our current case statement and the custom function. As you can see ive changed some format options because they are easier to read and use.

Case statement:
case
  when missing( DB2_DATETIME )
  then .
    
  when substr(left( DB2_DATETIME ), 1, 4) < "1582"
  then "01JAN1582:00:00:00.000000"dt
    
  when substr(left(DB2_DATETIME ), 1, 4) < "1926"
    or substr(left(DB2_DATETIME ), 1, 4) > "2095"
  then dhms(input(substr( DB2_DATETIME , 1,10), yymmdd10.), 0,0, input(substr( DB2_DATETIME , 12, 8), time8.))
    
  else dhms(input(substr( DB2_DATETIME , 1,10), yymmdd10.), 0,0, input(substr( DB2_DATETIME , 12, 15), time15.6))
end

Function:
function db2_to_sas_datetime(db2_datetime $);
  length sas_datetime 8
         year $4;

  year = substr(db2_datetime, 1, 4);
  
  if year < "1582"
  then
    sas_datetime = "01JAN1582:00:00:00.000000"dt ;
  else do;
    if "1926" < year < "2096"
    then
      sas_datetime = input(db2_datetime, ymddttm26.);
    else
      sas_datetime = input(substr(db2_datetime, 1, 19), ymddttm19.);
  end;

  return(sas_datetime);
endsub;

Im running these jobs in a SAS 9.3 environment.

[SASKiwi]

Just a thought but have you tried running your function in a DATA step and if so do you get a similar performance drop?

The reason I ask this is that SAS would have to translate your function into SQL if it is run in the SQL procedure, But if you run it in a DATA step this translation would not be required.

[ChrisNZ]

2 answers:

1) proc fcmp does add an overhead

2) you are comparing very different syntaxes

This is how you can see it:

* The function runs in 6s;

data _null_;

  do i=1 to 1e7;x=db2_to_sas_datetime(sas_datetime);end;

run;

*  The select statement runs in 0.1s;

data _null_;

do i=1 to 1e7;

select;

  when (missing( DB2_DATETIME )) t=.;

  when (substr(left( DB2_DATETIME ), 1, 4) < "1582")

    t= "01JAN1582:00:00:00.000000"dt;

  when (substr(left(DB2_DATETIME ), 1, 4) < "1926"

    or substr(left(DB2_DATETIME ), 1, 4) > "2095")

    t= dhms(input(substr( DB2_DATETIME , 1,10), yymmdd10.), 0,0, input(substr( DB2_DATETIME , 12, 8), time8.));

  otherwise t=dhms(input(substr( DB2_DATETIME , 1,10), yymmdd10.), 0,0, input(substr( DB2_DATETIME , 12, 15), time15.6));

end;

end;

run;

* The if test from the function runs in 1.6s on its own;

data _null_; 

do i=1 to 1e7;

  year = substr(db2_datetime, 1, 4);

  if year < "1582"

  then

    sas_datetime = "01JAN1582:00:00:00.000000"dt ;

  else do;

    if "1926" < year < "2096"

    then

      sas_datetime = input(db2_datetime, ymddttm26.);

    else

      sas_datetime = input(substr(db2_datetime, 1, 19), ymddttm19.);

  end;

end;

run;

[ChrisNZ]

If you want to keep using the function for its practicality, you should optimise its code.

[LinusH]

Just a thought, do you really have datetime data from before 1582 or after 2095, and consider them as correct?

Are those stored in DB2 as char? If so, you could just throw the input function using implicit pass-thru, chances are that SAS/ACCESS will convert it to DB2 SQL, hence improving performance.