topic Re: proc report compute macro variable in SAS Programming

proc report compute macro variable

radha009 — Sun, 22 Apr 2018 19:26:49 GMT

How to compute macro variable in proc report, the &varlist. is displaying all column names as one. how to call only one column name.

proc sq;

select name into :varlist from the dictionary.columns where memname='test';

proc report data=qtrresults

column ID test1 test2 &varlist. ;

define ID / display "ID";

define test1 /display "test1";

define &varlist. /display;

compute &varlist.;

if flag:_&varlist. =2 then

call define('dummy3',"style", "style=[font_style=italic background=yellow]");

end;

end comp;

Re: proc report compute macro variable

Reeza — Sun, 22 Apr 2018 19:34:01 GMT

Fix the PROC SQL query so that there's a space between the items. There are many examples on here on how to do that, or the documentation has examples.

@radha009 wrote:

How to compute macro variable in proc report, the &varlist. is displaying all column names as one. how to call only one column name.

proc sq;

select name into :varlist from the dictionary.columns where memname='test';

proc report data=qtrresults

column ID test1 test2 &varlist. ;

define ID / display "ID";

define test1 /display "test1";

define &varlist. /display;

compute &varlist.;

if flag:_&varlist. =2 then

call define('dummy3',"style", "style=[font_style=italic background=yellow]");

end;

end comp;

Re: proc report compute macro variable

Reeza — Sun, 22 Apr 2018 19:35:50 GMT

Its not clear if you're asking about the COLUMN or DEFINE statement. Note that a variables does not require a DEFINE statement, check what the default behaviour is first.

Re: proc report compute macro variable

Kurt_Bremser — Sun, 22 Apr 2018 19:37:46 GMT

Why are you putting a list of variables into your macrovar, when you only need the name of one?

Re: proc report compute macro variable

PaigeMiller — Sun, 22 Apr 2018 20:57:40 GMT

@radha009 wrote:

How to compute macro variable in proc report, the &varlist. is displaying all column names as one. how to call only one column name.

This is really impossible to say given the little information you have provided us. "Is displaying all column names as one"??? makes no sense, since you don't tell us what is displaying ... show us what you mean, show us the SASLOG or other indication of the problem.

Further, you need to make sure your SAS syntax is correct.

There is no "proc sq;"

The word "the" does not belong in "select name into :varlist from the dictionary.columns where memname='test';"

and so on

Re: proc report compute macro variable

radha009 — Sun, 22 Apr 2018 23:55:42 GMT

sorry for the typos and the wording.

I want to create a report to show the cells colored if the data doesn't match.

my data few variables are dynamic,every month it will be different but they have a number start with chg so i am putting all columns which starts with %chg%.

------------------------

data comp.check5;
   merge descope.load2sp_ds (in=ina) comp.check4;
   by Name CI_ID;
   if ina;
run;

proc sql noprint;
select name into :varlist separated by ' '
from dictionary.columns
where
libname = "COMP" and
memname = "CHECK1" and
name like '%CHG%';
quit;

proc report data=comp.check5 nowd;
column Name ID test1 test2 flag_: Impacted_Flag Validation_required Validation_status Validator_Name flag_:&varlist. Maint_Window;

define Name / display style(column)=[just=left cellwidth=3.1in]'Name';
define CI_ID / display style(column)=[just=left cellwidth=1.1in] 'ID';

define flag_: / noprint display;
define Impacted_Flag / display style(column)=[just=center cellwidth=1.1in] 'Impacted';

define var / computed display style(column)=[just=center cellwidth=1.1in];

compute Impacted_Flag;
          if FLAG_Impacted_flag = 2 then
            call define(_col_, "style", "style=[background=yellow]");
    endcomp;

compute var;
     do i = 1 to dim(var) ;
   if FLAG_:&varlist. = 2 then
            call define(_col_, "style", "style=[background=yellow]");
   end;
   endcomp;

Re: proc report compute macro variable

PaigeMiller — Mon, 23 Apr 2018 01:29:54 GMT

One thing you will have to do is loop over all the variable names in &varlist; this loop probably needs to be inside PROC REPORT.

You can't use a list of variable names in PROC Report the way you are doing it.

Re: proc report compute macro variable

Reeza — Mon, 23 Apr 2018 01:51:05 GMT

Change your data structure and use a variable with an ACROSS instead. Then you don't need a lot of this...possibly any.

sorry for the typos and the wording.

I want to create a report to show the cells colored if the data doesn't match.

my data few variables are dynamic,every month it will be different but they have a number start with chg so i am putting all columns which starts with %chg%.