CODEWITHSUNDEEP
sas
md2614
md2614

Reputation: 489

Remove single quotes in list of values in macro variable

I have a project with multiple programs. Each program has a proc SQL statement which will use the same list of values for a condition in the WHERE clause; however, the column type of one database table needed is a character type while the column type of the other is numeric.

So I have a list of "Client ID" values I'd like to put into a macro variable as these IDs can change, and I would like to change them once in the variable instead of in multiple programs.

For example, I have this macro variable set up like so and it works in the proc SQL which queries the character column: %let CLNT_ID_STR = ('179966', '200829', '201104', '211828', '264138');

Proc SQL part: ...IN &CLNT_ID_STR.

I would like to create another macro variable, say CLNT_ID_NUM, which takes the first variable (CLNT_ID_STR) but removes the quotes.

Desired output: (179966, 200829, 201104, 211828, 264138)

Proc SQL part: ...IN &CLNT_ID_NUM.

I've tried using the sysfunc, dequote and translate functions but have not figured it out.

Upvotes: 0

Views: 774

Answers (2)

Richard
Richard

Reputation: 27516

Three other ways to remove single quotes are COMPRESS, TRANSLATE and PRXCHANGE

%let CLNT_ID_STR = ('179966', '200829', '201104', '211828', '264138');

%let id_list_1 = %sysfunc(compress (&CLNT_ID_STR, %str(%')));
%let id_list_2 = %sysfunc(translate(&CLNT_ID_STR, %str( ), %str(%')));
%let id_list_3 = %sysfunc(prxchange(%str(s/%'//), -1, &CLNT_ID_STR));

%put &=id_list_1;
%put &=id_list_2;
%put &=id_list_3;

----- LOG -----

ID_LIST_1=(179966, 200829, 201104, 211828, 264138)
ID_LIST_2=( 179966 ,  200829 ,  201104 ,  211828 ,  264138 )
ID_LIST_3=(179966, 200829, 201104, 211828, 264138)

It really doesn't matter that TRANSLATE replaces the ' with a single blank () because the context for interpretation is numeric.

Upvotes: 1

Quentin
Quentin

Reputation: 6378

TRANSLATE doesn't seem to want to allow a null string as the replacement.

Below uses TRANSTRN, which has no problem translating single quote into null:

1    %let CLNT_ID_STR = ('179966', '200829', '201104', '211828', '264138');
2    %let want=%sysfunc(transtrn(&clnt_id_str,%str(%'),%str())) ;
3    %put &want ;
(179966, 200829, 201104, 211828, 264138)

It uses the macro quoting function %str() to mask the meaning of a single quote.

Upvotes: 1

Related Questions