SAS function compress() in PROC SQL

[marieK]

Hallo,

I would like to improve the run time of my SAS program. For that I am tryig to translate my SAS code into SQL code. Does anyone knows if there is an aquivalent procedure in SQL for the SAS function compress()? I would like to extract specified ICD codes from a reference list and want eliminiate special characters from the codes (+-!.#*). see code below:

          PROC SQL;

                        connect to oracle (user=&user. password="&password." path=&path.);

                              create table test as

                                 select ID, INP_ID, DIAG

                             from connection to oracle

                             (select ID, INP_ID, DIAG

                                from lib.table_hosp)

                            where (upcase(strip(compress(DIAG,"+-!.#*"))) in (select upcase(strip(compress(ICD_10,"+-!.#*"))) from ref.icd));

                    disconnect from oracle;

             QUIT;    

to improve the run time I would like to perform the query directly in oracle like:

               PROC SQL;

                        connect to oracle (user=&user. password="&password." path=&path.);

                        execute(

                              create table test as

                                 select ID, INP_ID, DIAG

                                from lib.table_hosp

                            where (TRIM(upper(REGEXP_REPLACE(DIAG,'[-!.#*+]',''))) in (select TRIM(upper (REGEXP_REPLACE(ICD,'[-!.#*+]',''))) from ref.icd)

                                   and IDNUM in(select PATIENTID from safeguard.Patients_out_dus_cov_mn))

                        )

                    by oracle;

                    disconnect from oracle;

                 QUIT;            

I tried 1000 of possibilities and googled around and found the procedure REGEXP_REPLACE() but nothing works out.

Do you have any idea?

Thanks in advance!

Marie

[LinusH]

Since you write your code in native Oracle SQL, I believe this is not the proper forum. I suggest that you post this on an Oracle specialized forum. Good luck!

SAS/ACCESS(R) 9.4 for Relational Databases: Reference, Second Edition

[marieK]

Hi Linus,

thanks! I will try my luck there

[jakarman]

I you want to improve run-time execution....

This is requiring to understand the effects of your coding and way of analyses well.

Some of this parts are touched in the advanced SAS programming goals. Do not expect the simple minded approaches like ... do it in oracle use, proc sql, etc etc solving that problem. There is some: Conservation of energy - Wikipedia, the free encyclopedia (law of conservation of energy)

with tuning&performance within IT there should be "a law of conservation of misery".

You can put more processing in a DBMS hoping it is more powerfull. that is more with that as "in databaseprocessing" a database redesign or one of another type to the ultimate technical solution.

You could also improve your processing in more simple steps like better tuning config setttings combining logical steps to minimize IO.

All this human effort only can be justified where the win/results must come from a repeatable proces. By that you are in the area of a software engineer having to deal with change test and monitor processes.