Macro variable value with single quote

[David_Billa]

I've the code as follows. I"m getting the error if I execute it. Any help to resolve the error?

If you look at the value closing run' insurance, I've single quote after the string run which I want in the result.

%let rep_run=%upcase('closing run' insurance','insurance','secondary closing run insurance','business insurance','RI_BCT');
%put &=rep_run;

Error message:

26         %let rep_run=%upcase('closing run' insurance','insurance','secondary closing run insurance','business
                                                       ___         ___                                                    ___
                                                       49          49                                                     49
26       ! insurance','RI_BCT');
ERROR: Open code statement recursion detected.

Desired Result:

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

[japelin]

First of all, %upcase cannot be used in open code.

It is used in %Macro-%Mend block.

Therefore, I think the easiest way is to enclose the string you want to process in double quotation marks and assign it to a macro variable using call symput in data step.

data _null_;
  call symput('rep_run',upcase("'closing run' insurance','insurance','secondary closing run insurance','business insurance','RI_BCT'"));
run;

[David_Billa]

@japelin I just ran your code. Is this not an error?

Log:

26       !  data _null_;
27           call symput('rep_run',upcase("'closing run' insurance','insurance','secondary closing run insurance','business
27       ! insurance','RI_BCT'"));
28         run;

NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      

29         
30         %put ####&rep_run.;
NOTE: Line generated by the macro variable "REP_RUN".
30         'CLOSING RUN' INSURANCE','INSURANCE','SECONDARY CLOSING RUN INSURANCE','BUSINESS INSURANCE','RI_BCT'
                                  ___         ___                                                    ___
                                  49          49                                                     49

[Kurt_Bremser]

You must always take care that the code created through the resolution of macro triggers is valid. As you can see, here it is not.

If the resolution of a macro variable causes problems is determined by the context in which the variable is resolved. What do you intend to do with this macro variable?

For instance, this works:

%put "&rep_run";

because the unbalanced single quotes are alleviated by the whole string being enclosed in double quotes.

[David_Billa]

@Kurt_Bremser I want to use the macro variable value to use in SQL Server tables to check for values. As SQL server will consider only single quote for the string, how to tackle it now?

I would like to know how to handle case when here when you want to compare the macro variable values with SQL server field values?

Proc Sql noprint;
	Connect to SQLSERVER (DATAsrc=&DATASRC. AUTHDOMAIN="XXXXX." dbMax_text=32767);
	Execute ( Insert into Meta_cntrl Select Distinct
		NULL as org_id
	Case 
		When (rep_run) in (&rep_run.) 
		then 'PI_PPT' 
	else  ' '
	end
    as reporting_value;
	) by SQLSERVER;
	;
	Disconnect from SQLSERVER;
quit;

[japelin]

Then try to submit this and then submit your proc sql code.

data _null_;
  call symput('rep_run',upcase("'closing run'' insurance','insurance','secondary closing run insurance','business insurance','RI_BCT'"));
run;

[David_Billa]

Ok. Can I call the macro variable in proc SQL connect to SQL server without
any double quotes around?

[japelin]

yes.

As an example, please check that the following three sql statements all give the same result.

data _null_;
  call symput('rep_run',upcase("'closing run'' insurance','insurance','secondary closing run insurance','business insurance','RI_BCT'"));
run;

data test;
  length rep_run $30;
  rep_run="CLOSING RUN' INSURANCE";output;
  rep_run='CLOSING RUN'' INSURANCE';output;
  rep_run='RI_BC';output;
  rep_run='BUSINESS INSURANCE';output;
run;


Proc Sql ;
    Select rep_run
          ,(Case 
	         	When (rep_run) in (&rep_run.) 
		            then 'PI_PPT' 
	            else  ' '
	        end
            ) as x
    from test;

Proc Sql ;
    Select rep_run
          ,(Case 
	    	    When (rep_run) in ('CLOSING RUN'' INSURANCE'
                                  ,"BUSINESS INSURANCE"
                                  ,"INSURANCE"
                                  ,'RI_BCT')
		            then 'PI_PPT' 
	            else  ' '
	        end
            ) as x
    from test;

Proc Sql ;
    Select rep_run
          ,(Case 
	    	    When (rep_run) in ("CLOSING RUN' INSURANCE"
                                  ,"BUSINESS INSURANCE"
                                  ,"INSURANCE"
                                  ,'RI_BCT')
		            then 'PI_PPT' 
	            else  ' '
	        end
            ) as x
    from test;
quit;

[David_Billa]

@japelin your code is working fine with the SAS datasets but not with the SQL tables. I'm getting the similar error as I mentioned before.

217        'CLOSING RUN(VFA)','SECONDARY CLOSING RUN,CLOSING RUN(BFA)','IMPACT OF OVERRETURN ON CPM,'BASELINE FOR INITAL                           ___                           ___                          ___                          ___
                           49                            49                           49                           49

My program is similar as below, but the source table is from SQL.

Proc Sql ;
    Select rep_run
          ,(Case 
	         	When (rep_run) in (&rep_run.) 
		            then 'PI_PPT' 
	            else  ' '
	        end
            ) as x
    from sqltbl.test;

[Kurt_Bremser]

If the SQL server accepts only single quotes for strings, use double quotes in the string.

Or try to use 2 consecutive single quotes in the string, this might be possible.

[yabwon]

Two things here,

1) To handle single quote in a text string surrounded by single quotes you have to escape it by doubling it, e.g.

data _null_;
  x = "ab'cd"; /* no need for escape */
  y = 'ab''cd'; /* escape ' by doubling it */
run;

By the way, for handling single quote, you can use my code from this thread:

https://communities.sas.com/t5/SAS-Programming/Macro-Variable-values-to-enclose-with-single-quote/m-...

data _null_;
  call symputx("rep_run", "closing run' insurance,insurance,secondary closing run insurance,business insurance,RI_BCT");
run;
%put rep_run=%superq(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=%superq(rep_run);

2) The Error you have in the code is because the single quote in the text "closing run' insurance". It is "breaking SAS quotes handling" and the 

---
49

is because SAS see text:

','i

and interprets it as "possible in the future" special text string like these:

'02sep2021'd 
'12:34:56't 
'1001101101'b
'some crazy name'n

are.

All the best

Bart

[David_Billa]

@yabwon How do you handle your second data step If I want to create multiple macro variables in the first data step as shown below?

data _null_;
  call symputx("rep_run", "closing run' insurance,insurance,secondary closing run insurance,business insurance,RI_BCT");
  call symputx("chk_run", "insurance,secondary closing run insurance,business insurance,PI_BCT");
  call symputx("chk_date_sql", "RI_TCT");
run;

[yabwon · Accepted solution]

How about a loop?

data _null_;
  call symputx("rep_run", "closing run' insurance,insurance,secondary closing run insurance,business insurance,RI_BCT");
  call symputx("chk_run", "insurance,secondary closing run insurance,business insurance,PI_BCT");
  call symputx("chk_date_sql", "RI_TCT");
run;
%put rep_run=%superq(rep_run);
%put chk_run=%superq(chk_run);
%put chk_date_sql=%superq(chk_date_sql);

data _null_;
  length text str rep_run $ 32767 x $ 32;

  do x = "rep_run","chk_run","chk_date_sql";
    text = symget(x);

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

    call symputx(x, rep_run, "G");
    rep_run = " ";
  end;
run;
%put rep_run=%superq(rep_run);
%put chk_run=%superq(chk_run);
%put chk_date_sql=%superq(chk_date_sql);

Bart

[David_Billa]

@yabwon Perfect.Your code is working fine. I have two more questions before I close this topic.

- why there is a two single quote after the string 'CLOSING RUN. Have you noticed it?

49         %put rep_run=%superq(rep_run);
rep_run='CLOSING RUN'' INSURANCE','INSURANCE','SECONDARY CLOSING RUN INSURANCE','BUSINESS INSURANCE','RI_BCT'

- Can we accomplish this task only by data step? any alternate methods?

[yabwon]

Hi,

1) double single quote is to mask that one single quote "between" single quotes. Run the code and you will see that double single quote in code is converted into single one by the data.

data test;
  x = "ab'cd"; /* no need for escape */
  y = 'ab''cd'; /* escape ' by doubling it */
run;

 You do that kind of "escaping" when you are passing text data containing single quotes in Oracle or SQLserver.

In your macrovariable you had: 

CLOSING RUN' INSURANCE

so after adding single quotes around the text [we have the quote() function in the data step] that one inside had to be doubled.

2) Using data step in this case is more convenient because you don't have to worry about that "special characters".

All the best

Bart