SAS Macro Variable having commas to be converted to single quotes with commas

1
votes

Could someone help me how I can get a macro variable updated with single quotes.

I am getting numbers as comma separated values from a Dataset like below:

2108008080, 2108008081, 2108888082, 2108858090, 213856345 etc I have to pass each of these records in a SQL where condition as Phone_numbers in (&current_macro_variable). To do this I have to add single quotes around each value as the Phone_numbers columns is a character field of Oracle DB.

Any help is highly appreciated. I could not get much help from SAS communities.

3
sassas-macro
How are you getting the comma separated list in the first place? It will most likely be easiest to add the quotes at this stage – Longfish
What is your reason not to use double quotes? Then the solution would be easy: %let macroQuoted = "%sysfunc(tranwrd(%str(&macroVar),%str(, ),%str(", ")))"; – Dirk Horsten
I couldn't find your question on SAS communities, can you please show where you posted it there? – Reeza
@DirkHorsten Given the explained use case, I would guess this is being passed to a SQL box that expects only single quotes. – Joe
Yes Joe. Tom answer helped me. I tried 1C yesterday but not 1aC in my CATQ function – Naga Vemprala

3 Answers

4
votes

Use PROC SQL and the select ... into syntax :

/* Build list of quoted phone numbers separated by a comma */
proc sql ;
  select cats("'",phone_number,"'") into :PHONELIST separated by ',' 
  from phonedata ;
quit ;

/* Then pass it into your Oracle query... */
proc sql ;
  select vars
  from ora.table
  where phone in(&PHONELIST) ;
quit ;
3
votes

Did you try the CATQ() function? Use the 1 modifier to generate single quotes. Use the a modifier to get all terms quoted. Use the c modifier to generate commas as the output delimiter.

%let x=2108008080, 2108008081, 2108888082, 2108858090, 213856345;
%put %sysfunc(catq(1ac,&x));

Which produces:

'2108008080','2108008081','2108888082','2108858090','213856345'
0
votes

The quote function should do the trick. You have to go through all of the words in the stringLine and add quotes.

%let macroVar = %str(2108008080, 2108008081, 2108888082, 2108858090, 213856345);

data _null_;
    n = count("&macroVar",",");
    length 
        quotedword $2000.;
    quotedword = "";
    do i = 1 to n+1;
        word = scan("&macroVar",i,",");
        y = quote(compress(word));
        quotedword = strip(quotedword) || "," || strip(y);
    end;
    quotedword = substr(quotedword,2); /* removing first comma from beginning */
    call symputx("macroVarUpdated",quotedword);
run;

%put &macroVar;
%put &macroVarUpdated;