BookmarkSubscribeRSS Feed
marieK
Obsidian | Level 7

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

3 REPLIES 3
LinusH
Tourmaline | Level 20

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

Data never sleeps
marieK
Obsidian | Level 7

Hi Linus,

thanks! I will try my luck there Smiley Happy

jakarman
Barite | Level 11

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.     

---->-- ja karman --<-----

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 2839 views
  • 0 likes
  • 3 in conversation