Need help with a macro within a proc report statement

[biglerc]

Hi everyone!

I was wondering if anyone has an idea that can help me with this issue I'm having.  I have a proc report statement that will write out a dataset that will vary in the number of columns each time.  I've figured out how to accommodate this for the Columns statement, but not for the define statement.  Any ideas or guidance that you can share with me?

Here is what I have now.  The dayvrs2 macro variable holds the number of columns that need to be addressed.  I haven't figured out a way to dynamically write out the Define statements, so I'm having to check each time I run the program and manually type all the Define statements.

proc report data=cm_combined nowd headline headskip spacing=3 split='|' missing ls=250 ps=42 ;

column
     %macro columns;
          %do i = 1 %to &dayvars2;
               day&i._flag
          %end;
     %mend columns;
     %columns
     _all_ ;

define cutoff / noprint;
Define day1_flag / noprint;
Define day2_flag / noprint;
Define day3_flag / noprint;
Define day4_flag / noprint;
Define day5_flag / noprint;
Define day6_flag / noprint;
Define day7_flag / noprint;
Define day8_flag / noprint;

....

Define day90_flag / noprint;

/* Color coding */
%macro colors;
%do i = 1 %to &dayvars2;
COMPUTE day&i;
if day&i._flag.sum = 1 then do;
call define(_col_,'style','style={background=lightyellow}');
end;
ENDCOMP;
%end;
%mend colors;
%colors;

run;

Thanks!

[Tom]

First step is to be very clear what code is it that you need generated.

Then you can define a macro that generates that code.

Do if you want to generate multiple DEFINE statements then you can probably do that with a %DO loop.

%do i=1 %to &dayvars2;
  define day&i._flag / noprint;
%end;

You should not place macro definition in middle of a data step. It will be defined before the data step starts running anyway so if you place the code before the data step then the programmer is less likely to get confused about the execution order.

You can either use two macros to generate the two dynamic parts of your step.  Or have the macro generate the whole step.  Which is best depends on how you are using this.

%macro column_names;
  %local i;
  %do i = 1 %to &dayvars2;
 day&i._flag
  %end;
%mend column_names;

%macro defines;
  %local i;
%do i=1 %to &dayvars2;
define day&i._flag / noprint;
  %end;
%mend defines;

%macro colors;
  %local i;
  %do i = 1 %to &dayvars2;
COMPUTE day&i;
  if day&i._flag.sum = 1 then do;
    call define(_col_,'style','style={background=lightyellow}');
  end;
ENDCOMP;
%end;
%mend colors;


proc report data=cm_combined nowd headline headskip spacing=3 split='|' missing ls=250 ps=42 ;
  column %column_names _all_;
  define cutoff / noprint;

%defines
%colors

run;

[biglerc]

Thank you so much, Tom!

What I'm trying to do is have it generate the Define code for me dynamically, depending on the value of &dayvars2, like I am doing for the columns statement. I think the reason the Define is proving tricky is because each Define statement needs to end with a semicolon and I need SAS to recognize that as a separate statement. The Columns is easier because it's just a list of variables.

[Tom]

Semi-colons (that are not used to mark the end of a macro statements) are just more text to the macro processor to emit.  So the macro can generate any number of semi-colons you need.

I have corrected my original response to include the missing %DO statement.

%do i=1 %to &dayvars2;

[PaigeMiller]

UNTESTED CODE

%macro columns;
          %do i = 1 %to &dayvars2;
               day&i._flag
          %end;
%mend columns;
%macro define;
    %do i=1 %to &dayvars2;
         define day&i._flag/noprint;
     %end;
%mend define;


proc report data=cm_combined nowd headline headskip spacing=3 split='|' missing ls=250 ps=42 ;
column
     %columns
     _all_ ;

define cutoff / noprint;
%define
run;

[biglerc]

Thanks, Paige! I tried that, actually, and it doesn't work. I think because I need SAS to recognize a semicolon after each Define statement and run it.

[PaigeMiller]

If it doesn't work, it is easily fixed, and the general structure works. In my code, I did put a semi-colon for each DEFINE statement.

A statement such as your "it doesn't work" is not useful, as there's no way to move forward and fix the problem. You need to show us the LOG and the errors there. First use the command options mprint; and then run it again and show us the LOG, by copying the log as text and pasting it into the window that appears when you click on the </> icon, this preserves the formatting in the log. This is mandatory, I no longer try to decipher logs that are presented if you don't follow these instructions to preserve the formatting.

[ballardw]

It is a very good idea to keep macro definition code outside of a procedure or data step. For one, there can be considerable timing issues depending on what the macro does. Second it makes it very hard to test just what the macro alone is doing.

[biglerc]

Thanks, Paige,  I thought this was a friendly forum, I didn't realize it got hostile!

Thanks to everyone for their suggestions, I'll bow out of this community.

[Tom]

---

@biglerc wrote:

Thanks, Paige,  I thought this was a friendly forum, I didn't realize it got hostile!

Thanks to everyone for their suggestions, I'll bow out of this community.

---

Don't get mad at Paige.  We just need to see more information to be able to give more precise answers.  Without the details the answer are vague and will be hard for you to understand and apply.

[ballardw]

---

@biglerc wrote:

Thanks, Paige,  I thought this was a friendly forum, I didn't realize it got hostile!

Thanks to everyone for their suggestions, I'll bow out of this community.

---

You are likely not aware that we deal with a lot of people who say "It doesn't work" and provide no details as to what actually happened. This happens often enough I have a macro for the forum that slugs in the basic requests.

Amazingly when the questioner actually posts the details we often find: typos in the code reported in the log;

details in the log that do not match the supposedly submitted code such as data set names, variable names or even procedure names.

Please go to an auto-mechanic with your car and say "it doesn't work". I'll place a fairly large stack of  $$$ that the first thing you will hear is a question like "what is it doing" or similar to help diagnose your problem. That is what we are doing when we ask for log, data and provide suggested options like MPRINT to get details of your problem.

[Kurt_Bremser]

You suffer from bad data layout. If you had one flag and one day variable, you'd use day as ACROSS in PROC REPORT, and let the procedure do all the work automatically, no macro needed.
Maxim 19: Long Beats Wide.