topic Re: SAS function compress() in PROC SQL in SAS Procedures

SAS function compress() in PROC SQL

marieK — Fri, 06 Sep 2013 07:44:19 GMT

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

Re: SAS function compress() in PROC SQL

LinusH — Fri, 06 Sep 2013 07:58:11 GMT

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

Re: SAS function compress() in PROC SQL

marieK — Fri, 06 Sep 2013 08:25:56 GMT

Hi Linus,

thanks! I will try my luck there

Re: SAS function compress() in PROC SQL

jakarman — Fri, 06 Sep 2013 11:10:09 GMT

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.