Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

Treating `[` as a literal when replacing in a string within SAS proc SQL using the translate function

I am using SAS version 9.4_M5. I have a data file in SAS with a column of values with format of 08.42.18 hrs __[0001122].gif.
I am wanting to replace the [ with [[]. So the value of 08.42.18 hrs __[0001122].gif would be changed to 08.42.18 hrs __[[]0001122].gif.

Below is an SQL test (note: I’m simply using sashelp.class as an example table since I am hard-coding the values into the SQL for this example so anyone with SAS can run it).

%_eg_conditional_dropds(testcase);
PROC SQL;
   CREATE TABLE testcase AS 
   SELECT 
   (translate('08.42.18 hrs __[0001122].gif','ffd','gif')) as t1,
   (translate('08.42.18 hrs __[0001122].gif','[[]','[')) as t2,
   (translate('08.42.18 hrs __[0001122].gif',']]',']')) as t3,
   (translate('08.42.18 hrs __[0001122].gif','blah','[[]')) as t4,
   (translate('08.42.18 hrs __[0001122].gif','him','\\[')) as t5,
   (translate('08.42.18 hrs __[0001122].gif','him','\[')) as t6
   FROM sashelp.class
 QUIT;

/* value of t1 changed to `08.42.18 hrs __[0001122].ffd` */
/* value of t2 stays the same `08.42.18 hrs __[0001122].gif` */
/* value of t3 stays the same `08.42.18 hrs __[0001122].gif` */
/* value of t4 gets weird and changes to `08.42.18 hrs __l0001122a.gif` */
/* value of t5 changes `[` to `m` */
/* value of t6 changes `[` to `i` */

It seems the translate function is treating the brackets as regex somehow but I was not able to find anything in the docs about it and the behavior is odd. I have not figured out how to force the translate function to treat the [ as a literal. Any ideas? – thank you

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

>Solution :

Use tranwrd instead.

data test;
    string = tranwrd('08.42.18 hrs __[0001122].gif', '[', '[[]');
run;

08.42.18 hrs __[[]0001122].gif

Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading