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);
... View more