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'
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
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;
@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
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.
@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;
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;
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;
@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;
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.
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:
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
@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;
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
@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?
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.