topic Re: Macro Variable values to enclose with single quote in SAS Programming

Macro Variable values to enclose with single quote

David_Billa — Wed, 01 Sep 2021 10:31:52 GMT

Assume I've the macro variable as follows.

%let rep_run= closing run insurance,insurance,secondary closing run insurance,business insurance,RI_BCT;

Now I want to create one new macro variable (e.g. rep_run_values ) by using the macro variable rep_run and I want the value to be enclosed with single quote as mentioned below.

'closing run insurance','insurance','secondary closing run insurance','business insurance','RI_BCT'

Any help?

Re: Macro Variable values to enclose with single quote

Tom — Wed, 01 Sep 2021 10:39:24 GMT

%let rep_run= closing run insurance,insurance,secondary closing run insurance,business insurance,RI_BCT;
%let new=%unquote(%str(%')%qsysfunc(tranwrd(%superq(rep_run),%str(,),','))%str(%'));
%put &=new;

405   %let rep_run= closing run insurance,insurance,secondary closing run insurance,business insurance,RI_BCT;
406   %let new=%unquote(%str(%')%qsysfunc(tranwrd(%superq(rep_run),%str(,),','))%str(%'));
407   %put &=new;
NEW='closing run insurance','insurance','secondary closing run insurance','business insurance','RI_BCT'

Convert the commas to ',' and add quotes before and after. Once the string is quoted there is no longer any need for macro quoting.

Re: Macro Variable values to enclose with single quote

PaigeMiller — Wed, 01 Sep 2021 10:44:10 GMT

You can use the QLIST macro

https://github.com/sasutils/macros/blob/master/qlist.sas

Re: Macro Variable values to enclose with single quote

David_Billa — Wed, 01 Sep 2021 10:56:10 GMT

@Tom @PaigeMiller Great! I do have one more question. How to convert the strings to upper case after enclosing it with single quote.

Desired result is,

'CLOSING RUN INSURANCE','INSURANCE','SECONDARY CLOSING RUN INSURANCE','BUSINESS INSURANCE','RI_BCT'

Re: Macro Variable values to enclose with single quote

PaigeMiller — Wed, 01 Sep 2021 10:57:22 GMT

%let new = %upcase(&new);

Re: Macro Variable values to enclose with single quote

yabwon — Wed, 01 Sep 2021 11:02:03 GMT

Just for fun, data step approach, with upcase.

%let rep_run= closing run insurance,insurance,secondary closing run insurance,business insurance,RI_BCT;
%put &=rep_run.;

data _null_;
  length text str rep_run $ 32767;
  text = symget("rep_run");

    do _N_= 1 to countw(text, ",");
      str = scan(text, _N_, ",");
      rep_run = catx(",", rep_run, quote(upcase(strip(str)),"'"));
    end;

  call symputx("rep_run", rep_run, "G");
run;

%put &=rep_run.;

[EDIT:] If the macro variable contains special characters, e.g. % or &, the code works too.

All the best

Bart

Re: Macro Variable values to enclose with single quote

Quentin — Wed, 01 Sep 2021 12:18:05 GMT

See also Richard DeVenezia's %seplist macro function. It does that and a lot more. Very handy.

https://www.devenezia.com/downloads/sas/macros/index.php?m=seplist

Re: Macro Variable values to enclose with single quote

Ksharp — Wed, 01 Sep 2021 12:43:02 GMT

%let rep_run= closing run insurance,insurance,secondary closing run insurance,business insurance,RI_BCT;
%let new=%str(%')%qsysfunc(prxchange(s/%str(,)/%str(',')/,-1,%bquote(%upcase(&rep_run))))%str(%');
%put &=new;

Re: Macro Variable values to enclose with single quote

Tom — Wed, 01 Sep 2021 12:43:56 GMT

Note there is a %QUPCASE() function.

Re: Macro Variable values to enclose with single quote

Ksharp — Wed, 01 Sep 2021 12:49:54 GMT

Sure . I just realize OP need UPCASE it ,after %bquote() .

Re: Macro Variable values to enclose with single quote

David_Billa — Wed, 01 Sep 2021 13:04:19 GMT

@Tom If I run the code below, I could see a some blanks in the value of macro variable rprun_pi_val. How to remove the blanks?

%let rprun_pi=main close run(VFB),secondary close run,main close run(BBB),impact of overreturn on CSP,baseline for inital recognition(VFB),                    Coverage Units new business,main close run mortality,main close run longevity,main close run lapse down,secondary close run mortality,
	secondary close run longevity,secondary close run lapse down;
%let rprun_pi_val=%upcase(%unquote(%str(%')%qsysfunc(tranwrd(%superq(rprun_pi),%str(,),','))%str(%')));
%put &=rprun_pi_val;

Results:

26         %let rprun_pi=main close run(VFB),secondary close run,main close run(BBB),impact of overreturn on CSP,baseline for inital
26       !  recognition(VFB),                    Coverage Units new business,main close run mortality,main close run longevity,main
26       ! close run lapse down,secondary close run mortality,
27         	secondary close run longevity,secondary close run lapse down;
28         %let rprun_pi_val=%upcase(%unquote(%str(%')%qsysfunc(tranwrd(%superq(rprun_pi),%str(,),','))%str(%')));
29         %put &=rprun_pi_val;
RPRUN_PI_VAL='MAIN CLOSE RUN(VFB)','SECONDARY CLOSE RUN','MAIN CLOSE RUN(BBB)','IMPACT OF OVERRETURN ON CSP','BASELINE FOR INITAL 
RECOGNITION(VFB)','                    COVERAGE UNITS NEW BUSINESS','MAIN CLOSE RUN MORTALITY','MAIN CLOSE RUN LONGEVITY','MAIN 
CLOSE RUN LAPSE DOWN','SECONDARY CLOSE RUN MORTALITY','  SECONDARY CLOSE RUN LONGEVITY','SECONDARY CLOSE RUN LAPSE DOWN'

Desired results:

26         %let rprun_pi=main close run(VFB),secondary close run,main close run(BBB),impact of overreturn on CSP,baseline for inital
26       !  recognition(VFB),                    Coverage Units new business,main close run mortality,main close run longevity,main
26       ! close run lapse down,secondary close run mortality,
27         	secondary close run longevity,secondary close run lapse down;
28         %let rprun_pi_val=%upcase(%unquote(%str(%')%qsysfunc(tranwrd(%superq(rprun_pi),%str(,),','))%str(%')));
29         %put &=rprun_pi_val;
RPRUN_PI_VAL='MAIN CLOSE RUN(VFB)','SECONDARY CLOSE RUN','MAIN CLOSE RUN(BBB)','IMPACT OF OVERRETURN ON CSP','BASELINE FOR INITAL RECOGNITION(VFB)','COVERAGE UNITS NEW BUSINESS','MAIN CLOSE RUN MORTALITY','MAIN CLOSE RUN LONGEVITY','MAIN 
CLOSE RUN LAPSE DOWN','SECONDARY CLOSE RUN MORTALITY','SECONDARY CLOSE RUN LONGEVITY','SECONDARY CLOSE RUN LAPSE DOWN'

Re: Macro Variable values to enclose with single quote

Ksharp — Wed, 01 Sep 2021 13:19:50 GMT

%let rep_run= rprun_pi=main close run(VFB),secondary close run,main close run(BBB),impact of overreturn on CSP,baseline for inital recognition(VFB),                    Coverage Units new business,main close run mortality,main close run longevity,main close run lapse down,secondary close run mortality,
	secondary close run longevity,secondary close run lapse down;

%let new=%str(%')%qsysfunc(prxchange(s/\s*%str(,)\s*/%str(',')/,-1,%qupcase(&rep_run)))%str(%');
%put &=new;

Re: Macro Variable values to enclose with single quote

Tom — Wed, 01 Sep 2021 13:33:51 GMT

The best way is to not build the macro variable with the spaces to begin with.

%let rprun_pi=main close run(VFB),secondary close run,main close run(BBB);
%let rprun_pi=&rprun_pi,impact of overreturn on CSP,baseline for inital recognition(VFB);
%let rprun_pi=&rprun_pi,Coverage Units new business,main close run mortality,main close run longevity;
%let rprun_pi=&rprun_pi,main close run lapse down,secondary close run mortality;
%let rprun_pi=&rprun_pi,secondary close run longevity,secondary close run lapse down;

How did you build that original macro variable?

Couldn't you build it with the quotes to start with? For example if the list of values was in a dataset

proc sql noprint;
  select quote(trim(ds_var),"'") into :mvar separated by ',' 
    from have
  ;
quit;

If you are stuck with the list with extra spaces.

You could adapt the posted REGEX solution to include the spaces around the commas in the string to be replaced.
You could use one of the posted methods that scans the original list and builds the new one.

If you assign a macro variable with the result of %SCAN() then the leading and trailing spaces will not become part of the macro variable.

%let rprun_pi=main close run(VFB),secondary close run,main close run(BBB),impact of overreturn on CSP,baseline for inital recognition(VFB),                    Coverage Units new business,main close run mortality,main close run longevity,main close run lapse down,secondary close run mortality,
	secondary close run longevity,secondary close run lapse down;

%macro quoteit(mvar);
%local i sep word;
%do i=1 %to %sysfunc(countw(%superq(&mvar),%str(,)));
  %let word=%scan(%superq(&mvar),&i,%str(,));
  %if %length(&word) %then %do;&sep.%bquote('&word')%let sep=,;%end;
%end;
%mend quoteit;
%let rprun_pi_val=%quoteit(rprun_pi);
%put &=rprun_pi_val;