macro value not working in my data modify step

[KoVa]

I'm working on an update procedure for a printing process table. 

I'm updating certain values in table A with the corresponding value from another table. basically certain info that I want to enrich in my main table, but which is variable (client A belongs to store B, client B belongs to store C, so I want to add phone_number and other info from that store)

first: to find the corresponding columns in both tables I created a macro value which results in something like this if I use %put &ts_keepmod in the log:
'BRO_LOGO', 'BRO_NAME',  'BRO_PHONE', 'BRO_SIGN_PERSON', 'BRO_URL'

next I'm trying to use that macro value in my update step (using hash table, fastest way I think). 

that way I can automate my update procedure, because I will need it for other tables as well. 

data print_matrix;
if _n_=1 then do;
declare hash ud(dataset:'brinfo');
ud.defineKey('store_code');
ud.defineData(&ts_keepmod);
ud.defineDone();
end;
modify print_matrix;
rcUpdate = ud.find();
if rcUpdate=0 then replace;
run;

this is not working 

ERROR 386-185: Expecting an arithmetic expression.

ERROR 200-322: The symbol is not recognized and will be ignored.

ERROR 76-322: Syntax error, statement will be ignored.

However it works fine when I just paste the value of the ts_keepmod variable  from the log in that step. 

data print_matrix;
if _n_=1 then do;
declare hash ud(dataset:'brinfo');
ud.defineKey('sotre_code');
ud.defineData('BRO_LOGO', 'BRO_NAME',  'BRO_PHONE', 'BRO_SIGN_PERSON', 'BRO_URL');
ud.defineDone();
end;
modify print_matrix;
rcUpdate = ud.find();
if rcUpdate=0 then replace;
run;

I think I need to change the output type of my macro value? 

first I created it in a datastep with

call symputx('TS_KEEP',co,"G");

(I need this value for other reasons so can't change here)

then created my macro from above:

%let TS_KEEPMOD = %sysfunc(tranwrd(%bquote(&TS_KEEP),%str( ),%str(%', %')));
%let TS_KEEPMOD= %str(%'&TS_KEEPMOD.%');

this is doing my head in, especially because it works fine with the value pasted.

is it because of the %str ? any help greatly appreciated!

[andreas_lds]

Please re-run the code with option mprint enabled to see what really happens and post the log.

[KoVa]

thx for the quick response. 

here is the log when I use options symbolgen and mprint (doesnt seem to do much though that mprint, maybe I'm using it wrong)

26         options symbolgen mprint;
27         
28         


29         data print_matrix;
30           if _n_=1 then do;
31             declare hash ud(dataset:'brinfo');
32             ud.defineKey('BRO_NUMPRO');
33             ud.defineData(&ts_keepmod);
SYMBOLGEN:  Macro variable TS_KEEPMOD resolves to 'BRO_BCE', 'BRO_COLOR_MAIN', 'BRO_COLOR_SECOND', 'BRO_COLOR_TEXT', 
            'BRO_COLOR_TEXT_MAIN', 'BRO_COLOR_TEXT_TITLE', 'BRO_EMAIL_CLI', 'BRO_FAX', 'BRO_FSMA', 'BRO_LOGO', 'BRO_NAME', 
            'BRO_NUMPRO', 'BRO_PHONE', 'BRO_SIGN_PERSON', 'BRO_URL'
SYMBOLGEN:  Some characters in the above value which were subject to macro quoting have been unquoted for printing.
NOTE: Line generated by the macro variable "TS_KEEPMOD".
33         'BRO_BCE', 'BRO_COLOR_MAIN', 'BRO_COLOR_SECOND', 'BRO_COLOR_TEXT', 'BRO_COLOR_TEXT_MAIN', 'BRO_COLOR_TEXT_TITLE',
           _
           386
            _
            200
            76
33       ! 'BRO_EMAIL_CLI', 'BRO_FAX', 'BRO_FSMA', 'BRO_LOGO', 'BRO_NAME', 'BRO_NUMPRO', 'BRO_PHONE', 'BRO_SIGN_PERSON', 'BRO_URL'
ERROR 386-185: Expecting an arithmetic expression.

ERROR 200-322: The symbol is not recognized and will be ignored.

ERROR 76-322: Syntax error, statement will be ignored.

34             ud.defineDone();
ERROR: DATA STEP Component Object failure.  Aborted during the COMPILATION phase.
NOTE: The SAS System stopped processing this step because of errors.
2                                                          The SAS System                          10:10 Wednesday, January 26, 2022

NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      user cpu time       0.00 seconds
      system cpu time     0.00 seconds
      memory              355.43k
      OS Memory           25968.00k
      Timestamp           01/26/2022 01:44:02 PM
      Step Count                        168  Switch Count  0

[Kurt_Bremser]

How do you create the variable co in the data step that finally uses CALL SYMPUTX? Please post that code.

I think you will be better off creating TS_KEEPMOD in a SQL step.

[KoVa]

Hi Kurt,

thx for the quick response. 

here is my macro I created to compare 2 tables. 

I use it so I can determine type/truncation issues. 

%macro ts_compare (tbl1, tbl2, outp);
%if %TS_isblank(&outp) %then %let outp = compare;
	proc contents data=&tbl1 noprint out=tbd_ds1(keep=Name Type Length Label);
	run;

	proc contents data=&tbl2 noprint out=tbd_ds2(keep=Name Type Length Label);
	run;

	data tbd_ds1;
		set tbd_ds1;
		name = upcase(name);
	run;

	data tbd_ds2;
		set tbd_ds2;
		name = upcase(name);
	run;

	%sort(tbd_ds1, name);
	%sort(tbd_ds2, name);

	data &outp (drop= lab1 lab2);
		merge
			tbd_ds1(in=in1 rename=(Type=Typ1 Length=Len1 Label=Lab1))
			tbd_ds2(in=in2 rename=(Type=Typ2 Length=Len2 Label=Lab2));
		by Name;
		format match $40.;

		if in1 and in2;

		if typ1 = typ2 then
			do;
				if len1 = len2 then
					match = 'OK FULL MATCH';
				else if len2 > len1 then
					match ='CHECK LEN VAR TABLE2';
				else match = 'OK FULL MATCH';
			end;
		else
			do;
				if len1 = len2 then
					match = 'CHECK DATATYPE VAR TABLE2';
				else if len2 > len1 then
					match ='CHECK LEN AND DATATYPE TABLE2';
				else match = 'CHECK DATATYPE VAR TABLE2';
			end;
	run;

	data ts_tbd3 (keep= co);
		set &outp (where=(match ='OK FULL MATCH')) end= eof;
		by name  notsorted;
		length co $4000.;
		retain co;
		co = catx(' ',co,name);

		if eof then
			call symputx('TS_KEEP',co,"G");
	run;

	proc datasets noprint;
		delete tbd_ds1 tbd_ds2 ts_tbd3;
	run;
	%GLOBAL TS_KEEPSQL TS_KEEPMOD;
	%let TS_KEEPSQL = %sysfunc(tranwrd(%bquote(&TS_KEEP),%str( ),%str(, )));
	%let TS_KEEPMOD = %sysfunc(tranwrd(%bquote(&TS_KEEP),%str( ),%str(%', %')));
	%let TS_KEEPMOD= %str(%'&TS_KEEPMOD.%');

%mend ts_compare;

[Kurt_Bremser]

data ts_tbd3 (keep= co);
		set &outp (where=(match ='OK FULL MATCH')) end= eof;
		by name  notsorted;
		length co $4000.;
		retain co;
		co = catx(' ',co,name);

		if eof then
			call symputx('TS_KEEP',co,"G");
	run;

can be done in SQL as

proc sql noprint;
select quote(strip(name),"'") into :ts_keepmod separated by ","
from &outp
where match = 'OK FULL MATCH';
quit;

and immediately adds quotes and commas as needed.

Example using the names of SASHELP.CLASS as stored in DICTIONARY.COLUMNS:

 69         proc sql noprint;
 70         select quote(strip(name),"'") into :ts_keepmod separated by ","
 71         from dictionary.columns
 72         where libname = "SASHELP" and memname = "CLASS";
 73         quit;
 NOTE:  Verwendet wurde: PROZEDUR SQL - (Gesamtverarbeitungszeit):
       real time           0.00 seconds
       user cpu time       0.00 seconds
       system cpu time     0.00 seconds
       memory              5320.96k
       OS Memory           29092.00k
       Timestamp           26.01.2022 12:59:58 nachm.
       Step Count                        121  Switch Count  0
       Page Faults                       0
       Page Reclaims                     193
       Page Swaps                        0
       Voluntary Context Switches        0
       Involuntary Context Switches      0
       Block Input Operations            0
       Block Output Operations           0
       
 
 74         
 75         %put &ts_keepmod;
 'Name','Sex','Age','Height','Weight'

The resulting macro variable can now safely be used in the call of the DEFINEDATA method.

[Quentin]

Hi,

There is an old rule/guidance for the macro language.

If the code is correct in the log (meaning you can copy-paste and it works), but you get an error, and you used macro quoting functions, try %unquoting.  The macro language should automatically unquote values before they go to the SAS compiler, but sometimes this doesn't happen.  It's a known bug.

So try:

ud.defineData(%unquote(&ts_keepmod));

For more on this bug, see https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/mcrolref/p1k3cotqhvgwk0n105gyfe1cqg9m.htm  .  (The docs call it a "problem", not a bug. : )

[Quentin]

@KoVa  did you try %unquote?  I haven't reviewed your whole program, but it looks like exactly the example of the need to %unquote from the documentation.

1    %let TS_KEEPMOD= %str(%'Foo%');
2
3    data _null_;
4      val = &TS_KEEPMOD;
NOTE: Line generated by the macro variable "TS_KEEPMOD".
1     'Foo'
      -
      386
       -
       200
ERROR 386-185: Expecting an arithmetic expression.

ERROR 200-322: The symbol is not recognized and will be ignored.

5      put 'VAL =' val;
6    run;

NOTE: The SAS System stopped processing this step because of errors.

7
8    data _null_;
9      val = %unquote(&TS_KEEPMOD);
10     put 'VAL =' val;
11   run;

VAL =Foo

[KoVa]

yes, and it fixed the problem! thank you very much.

Can I tag more than one answer as solution? I tagged Kurt's because I've been meaning to review/rewrite those comparison macro's anyway. 

[Quentin]

Glad it worked, thanks for confirming. There can be only one solution. Fine to leave it as Kurt's. Many folks try to avoid macro quoting as much as possible, just to reduce complexity. Many folks try to avoid the macro language as much as possible for the same reason. : )

Just wanted to point out that it's a known problem of the macro language, and that there is an explainable cause and known solution.

[Kurt_Bremser]

It's not that I avoid macros at all cost, but I like to keep handling of special characters in DATA or SQL steps; the need to use multiple quoting and unquoting functions tends to tie my brain into knots, which I find rather painful 😉

Similarly, I like to do complex macro variable creations in a DATA _NULL_ step simply to avoid the "%SYSFUNC avalanche".

[Kurt_Bremser · Accepted solution]

I would also circumvent the use of PROC CONTENTS and temporary datasets:

%macro ts_compare (tbl1, tbl2, outp);
%if &outp = %then %let outp = compare;
%if %sysfunc(countw(&tbl1.,.)) = 1 %then %let tbl1=WORK.&tbl1.;
%if %sysfunc(countw(&tbl2.,.)) = 1 %then %let tbl2=WORK.&tbl2.;
%let tbl1 = %upcase(&tbl1.);
%let tbl2 = %upcase(&tbl2.);
%global ts_keepmod;

proc sql;
create table &outp. as
select
  t1.name,
  t1.type as typ1,
  t1.length as len1,
  t1.label as lab1,
  t2.type as typ2,
  t2.length as len2,
  t2.label as lab2,
  case
    when t1.type = t2.type and t1.length = t2.length
    then "OK FULL MATCH"
    when t1.type ne t2.type and t1.length = t2.length
    then "CHECK DATATYPE VAR TABLE2"
    when t1.type = t2.type and t1.length ne t2.length
    then "CHECK LEN VAR TABLE2"
    when t1.type ne t2.type and t1.length ne t2.length
    then "CHECK LEN AND DATATYPE TABLE2"
    else "CAN'T BE"
  end as match
  from dictionary.columns t1 inner join dictionary.columns t2
  on upcase(t1.name) = upcase(t2.name)
  where
    t1.libname = "%scan(&tbl1.,1,.)" and t1.memname = "%scan(&tbl1.,2,.)" and
    t2.libname = "%scan(&tbl2.,1,.)" and t2.memname = "%scan(&tbl2.,2,.)"
;

proc sql noprint;
select quote(strip(name),"'") into :ts_keepmod separated by ","
from &outp.
where match = 'OK FULL MATCH';
quit;

%mend;

[KoVa]

thank you very much for your help - I will adapt my macro's using your suggestions!