Unable to highlight cells in ODS tagsets.excelxp and proc report

[gs17]

I want to highlight those variables which are present as a value in variable VARLIST. For example in below input file AGE and HEIGHT are present as value in VARLIST Variable then in output file(.xls) AGE and HEIGHT variables should be highlighted.

Input:-

Name	Age	Height	FLAG	VARLIST
Joyce	11	51.3	UPDATED	AGE HEIGHT
Joyce	12	51	UPDATED	AGE HEIGHT
Thomas	11	57.5	NO CHANGE

Below is the code I have written and it is generating correct output without any error except cells are not getting highlighted:

%macro report_prep(indsn=,rptname=);

proc contents data = &indsn
out = _indsn_contents(keep =name type length label varnum)
varnum noprint;
run;
proc sort data = _indsn_contents;
by varnum;
run;
proc sql noprint;
select upcase(name) into :_columnvar separated by ' '
from _indsn_contents;
quit;

%do i=1 %to %length(&_columnvar); /*find out how many variables*/
%if %scan(&_columnvar, &i) ne %str() %then %do;
%let _columnvartot=&i;
%end;
%end;
/*%put &_columnvartot;*/
%do i=1 %to &_columnvartot;
/*assign the name of each variable to a macro variable*/
%let _columnvar&i = %scan(&_columnvar,&i);
%end;

ods html close;
ods listing close;
ods tagsets.excelxp file = "&rptLoc.\&rptName._&sysdate..xls" style = minimal;

/*options mlogic;*/

proc report data = &indsn nofs
style(header)={font_weight=bold font_size=10pt just=center
protectspecialchars=off};
column &_columnvar;
%do i=1 %to &_columnvartot;
%if %upcase("&&_columnvar&i") ne "VARLIST" %then %do;
define &&_columnvar&i / display;
%end;
%if %upcase("&&_columnvar&i") = "VARLIST" %then %do;
define &&_columnvar&i / noprint; /*do not include variable FLAG and VARLIST in output*/
%end;
%end;

%if %index(%upcase("&_columnvar"),VARLIST) > 0 %then %do;
%let i = 1;
%let tmp = %upcase(%scan(&_columnvar,&i));
%do %while (&tmp ne %str());
/*%put &tmp;*/
%if %upcase("&tmp") ne "FLAG" and %upcase("&tmp") ne "VARLIST" %then %do;

%put &tmp;
compute &tmp;
if findw(upcase(varlist),strip("&tmp")) > 0 then do;    
/*%put &tmp;*/
call define(_col_,'style','style={background=gold}') ;
end;
endcomp;
%end;
%let i=%eval(&i + 1);/*process the next variable in the variable list*/
%let tmp = %scan(&_columnvar,&i);
%end;
%end;

run;

ods tagsets.excelxp close;
%mend report_prep;

%report_prep(indsn=final,rptname=class);

[RW9]

This is effectively the code you are trying to get to:

data have;
  infile datalines dlm=",";
  length name flag varlist $200;
  input name $ age height flag $ varlist $;
datalines;
Joyce,11,51.3,UPDATED,AGE HEIGHT
;
run;

ods listing close;
ods rtf file=".../test.rtf";
proc report data=have nowd;
  columns _all_;
  compute age;
    if findw(varlist,"AGE") then call define(_col_,'style','style={background=gold}');
  endcomp;
  compute height;
    if findw(varlist,"HEIGHT") then call define(_col_,'style','style={background=gold}');
  endcomp;
run;
ods rtf close;

I am not looking into that mass of macro code.  A simple dataset of all disitinct values of varlist, e.g.:

AGE

HEIGHT

Then use a datastep call execute, to create the above code.   In the below example I use a do loop, but a dataset with each of the variables would work equally good and be expandable based on the data:

data have;
  infile datalines dlm=",";
  length name flag varlist $200;
  input name $ age height flag $ varlist $;
datalines;
Joyce,11,51.3,UPDATED,AGE HEIGHT
;
run;

data _null_;
  call execute('ods listing close; ods rtf file="&_sasws_./test.rtf"; proc report data=have nowd; columns _all_;');
  do i="AGE","HEIGHT";
    call execute(cats('compute age; if findw(varlist,"',i,'") then call define(_col_,'style','style={background=gold}'); endcomp;');
  end;
  call execute('run; ods rtf close;');
run;

[gs17]

Thanks for your feedback. I ran your code but getting below error. 

1417 data _null_;
1418 call execute('ods listing close; ods rtf file="&_sasws_./test.rtf"; proc report data=have nofs; columns _all_;');
1419 do i="AGE","HEIGHT";
1420 call execute(cats('compute age; if findw(varlist,"',i,'") then call define(_col_,'style','style={background=gold}'); endcomp;');
---------------------------- ---
49 49
-----
388
-----
76
NOTE 49-169: The meaning of an identifier after a quoted string might change in a future SAS release. Inserting white space between a quoted string and the succeeding identifier is recommended.

ERROR 388-185: Expecting an arithmetic operator.

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

1421 end;
1422 call execute('run; ods rtf close;');
1423 run;

NOTE: The SAS System stopped processing this step because of errors.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

[RW9]

Its hard to tell without test data but the line:

call execute(cats('compute age; if findw(varlist,"',i,'") then call define(_col_,'style','style={background=gold}'); endcomp;');

Should look more like:

call execute(cats('compute age; if findw(varlist,"',i,'") then call define(_col_,"style","style={background=gold}"); endcomp;'));

You will note that I replace single with double quotes (as around those are single quotes), and an extra ) at the end to properly finish the (. 

[Lenith]

Your comput should be on 'varlist' and all looping within that:

%macro report_prep(indsn=,rptname=);


proc contents data = &indsn
out = _indsn_contents(keep =name type length label varnum)
varnum noprint;
run;
proc sort data = _indsn_contents;
by varnum;
run;
proc sql noprint;
select upcase(name) into :_columnvar separated by ' '
from _indsn_contents;
quit;

%do i=1 %to %length(&_columnvar); /*find out how many variables*/
%if %scan(&_columnvar, &i) ne %str() %then %do;
%let _columnvartot=&i;
%end;
%end;
/*%put &_columnvartot;*/
%do i=1 %to &_columnvartot;
/*assign the name of each variable to a macro variable*/
%let _columnvar&i = %scan(&_columnvar,&i);
%end;

 

ods html close;
ods listing close;
ods tagsets.excelxp file = "\&rptName._&sysdate..xls" style = minimal;


options nomlogic mprint nosymbolgen;

proc report data = &indsn nofs
style(header)={font_weight=bold font_size=10pt just=center
protectspecialchars=off};
column &_columnvar;
%do i=1 %to &_columnvartot;
%if %upcase("&&_columnvar&i") ne "VARLIST" %then %do;
define &&_columnvar&i / display;
%end;
%if %upcase("&&_columnvar&i") = "VARLIST" %then %do;
define &&_columnvar&i / noprint; /*do not include variable FLAG and VARLIST in output*/
%end;
%end;

 
compute varlist;
%if %index(%upcase("&_columnvar"),VARLIST) > 0 %then %do;
%let i = 1;
%let tmp = %upcase(%scan(&_columnvar,&i));
%do %while (&tmp ne %str());
/*%put &tmp;*/
%if %upcase("&tmp") ne "FLAG" and %upcase("&tmp") ne "VARLIST" %then %do;

%put &tmp;

if findw(upcase(varlist),strip("&tmp")) > 0 then  
call define("&tmp","style","style={background=gold}") ;

%end;
%let i=%eval(&i + 1);/*process the next variable in the variable list*/
%let tmp = %scan(&_columnvar,&i);
%end;
%end;
endcomp;
run;


ods tagsets.excelxp close;
%mend report_prep;

%report_prep(indsn=a,rptname=class);