topic Re: How do I replace a characters in a string with a value from another variable? in SAS Programming

How do I replace a characters in a string with a value from another variable?

Pili1100 — Fri, 18 Nov 2022 17:29:36 GMT

Hi,

I would like to replace a generic part of a string and replace with value from one variable in another variable.





/*input*/
DATA input;
LENGTH LIB_TABLES $100;
INPUT LIB_TABLES;
DATALINES;
LIB_A.TABLE_1
LIB_B.TABLE_2
LIB_C.TABLE_3
;

RUN;

/*What I have*/
DATA have;
SET input;
LIB = SCAN( LIB_TABLES, 1, '.');
TABLES = REVERSE(SCAN(REVERSE(LIB_TABLES),1,'.'));
X_have= 'info_tables(LIBNAME=XXX, TABLE=XXX, Table info)';

RUN;



/*Desirable output*/
DATA want; 

LIB_TABLES = 'LIB_A.TABLE_1';
LIB		   = 'LIB_A';
TABLES	   = 'TABLE_1';
X_WANT	   = 'info_tables(LIBNAME=LIB_A,TABLE=TABLE_1, Table info)';
OUTPUT;

	
LIB_TABLES = 'LIB_B.TABLE_2';
LIB		   = 'LIB_B';
TABLES	   = 'TABLE_2';
X_WANT	   = 'info_tables(LIBNAME=LIB_B,TABLE=TABLE_2, Table info)';
OUTPUT;


LIB_TABLES = 'LIB_C.TABLE_3';
LIB		   = 'LIB_C';
TABLES	   = 'TABLE_3';
X_WANT	   = 'info_tables(LIBNAME=LIB_C,TABLE=TABLE_3, Table info)';
OUTPUT;
RUN;

How should I do it?

Many thanks,

Re: How do I replace a characters in a string with a value from another variable?

Cynthia_sas — Fri, 18 Nov 2022 20:10:10 GMT

Hi:

You are very close. Using one of the concatenate functions to join your text and your variable values will work. I'd recommend the CATT function, which trims blanks:

I also simplified your SCAN, since using a -1 tells SCAN to start scanning from the other end of the variable string, which is what your REVERSES were doing.

Cynthia

Re: How do I replace a characters in a string with a value from another variable?

fja — Fri, 18 Nov 2022 20:56:11 GMT

As you stated that you wanted to _replace_ a certain char group by a variable my suggestion would be using the tranwrd function . ... note the trim(). This was suggested by @Tom in this post

/*input*/ 
DATA input; 
LENGTH LIB_TABLES $100; 
INPUT LIB_TABLES; 
DATALINES; 
LIB_A.TABLE_1 
LIB_B.TABLE_2 
LIB_C.TABLE_3 
; 
RUN; 
 
/*What I have*/ 
DATA have; 
SET input; 
LIB = SCAN( LIB_TABLES, 1, '.'); 
TABLES = REVERSE(SCAN(REVERSE(LIB_TABLES),1,'.')); 
X_have= 'info_tables(LIBNAME=XXX, TABLE=YYY, Table info)'; 
x_want=tranwrd(tranwrd(x_have, "YYY", trim(TABLES)), "XXX", trim(LIB)); 
RUN; 
proc print data=have;

Re: How do I replace a characters in a string with a value from another variable?

Tom — Fri, 18 Nov 2022 20:52:55 GMT

To re-create your example does NOT require replacing any text.

Just GENERATE the text.

DATA input;
  LENGTH LIB_TABLES $100;
  INPUT LIB_TABLES;
DATALINES;
LIB_A.TABLE_1
LIB_B.TABLE_2
LIB_C.TABLE_3
;

data want;
  set input;
  length lib $8 tables $32 x_want $100 ;
  lib=scan(lib_tables,1,'.');
  tables=scan(lib_tables,-1,'.');
  x_want=cats('info_tables(libname=',lib,',table=',tables,',Table info)');
run;

Result

Obs     LIB_TABLES       lib     tables                           x_want

 1     LIB_A.TABLE_1    LIB_A    TABLE_1    info_tables(libname=LIB_A,table=TABLE_1,Table info)
 2     LIB_B.TABLE_2    LIB_B    TABLE_2    info_tables(libname=LIB_B,table=TABLE_2,Table info)
 3     LIB_C.TABLE_3    LIB_C    TABLE_3    info_tables(libname=LIB_C,table=TABLE_3,Table info)

Do you have an example where you actually need to replace text?

Re: How do I replace a characters in a string with a value from another variable?

Patrick — Fri, 18 Nov 2022 21:42:29 GMT

Pretty much the same as Tom already suggested with the one extension to also deal with one level table names.

DATA have;
  LENGTH LIB_TABLES $100;
  INPUT LIB_TABLES;
  DATALINES;
LIB_A.TABLE_1
LIB_B.TABLE_2
LIB_C.TABLE_3
TABLE4
;

data want;
  set have;
  length lib $8 tables $32 x_want $100;
  lib=scan(cats('WORK.',lib_tables),-2,'.');
  tables=scan(lib_tables,-1,'.');
  x_want=cats('info_tables(LIBNAME=',lib,', table=',tables,', Table info)');
run;

Re: How do I replace a characters in a string with a value from another variable?

Pili1100 — Sun, 20 Nov 2022 19:18:11 GMT

Hi,

I got so many good solutions from the community. Thank you for helping me. This one works as charm! 🙏

Re: How do I replace a characters in a string with a value from another variable?

Pili1100 — Sun, 20 Nov 2022 19:19:12 GMT

Thank you for the double-trim. Will come in handy for replacing in a string 😁

Re: How do I replace a characters in a string with a value from another variable?

Pili1100 — Sun, 20 Nov 2022 19:25:38 GMT

Thank you for helping me. Another good solution. 😊
Regarding your question, Do you have an example where you actually need to replace text? and the reason I need help with this because I was looking for a solution for a makro where I use lib and tables and I wanted to avoid to copy/pase the XXX.

It can be time consuming when it's 20 tables.

Re: How do I replace a characters in a string with a value from another variable?

Pili1100 — Sun, 20 Nov 2022 19:28:06 GMT

Hi,

Thanks for expanding the solution, having WORK as default. Real nice 👏