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.
... View more