Loop and variables set in proc report compute step

[deleted_user]

Can someone take a look below and see why my variables do not seem to be setting properly?

I can't seem to define variables and them use them to step though loops during the compute step in a proc report. any thoughts? The snippet of code below prints TEST WHILE LOOP!!!!! once to the log as well as TEST !!!!!. I'd expect if variables are setting properly that TEST !!!!! would not print and that the TEST WHILE LOOP would print 16 times.


proc report data=WORK.COLL_UTIL_DTL_RPT split='*' out=work.tmp;

column coll_id applydt,(etime_hrs dialer_hrs dialer_adjmt_hrs my_util_pct) ;

define coll_id / group 'Coll ID';
define applydt / across 'Date';
define etime_hrs / display 'ETime*Hours';
define dialer_hrs / display 'Dialer*Hours';
define dialer_adjmt_hrs / display 'Dialer*Adj Hours';
define my_util_pct / COMPUTED 'Utilization*pct';
*break after applydt / summarize skip ol;

compute my_util_pct;
myVar2='16JUL2008'd;
%mysecmac('16JUL2008'd);
%mysecmac(myVar2);

dt_End=15;
dt_Start=0;
cnt=0;
do while(dt_Start le dt_End);
dt_Start+1;
cnt+1;
%put TEST WHILE LOOP!!!!!;
end;

T=16;
if T le 15 then do;
T+1;
%put TEST !!!!!;
end;


endcomp;

run; Message was edited by: kingpin

[Tim_SAS]

You're mixing macro code and data step code. The macro statements are executed by SAS's macro processor before PROC REPORT runs. The macro statement is not controlled by the do while statement.

Here's a link to the SAS documentation about macros: http://support.sas.com/documentation/cdl/en/mcrolref/59526/HTML/default/getstart.htm

[Cynthia_sas]

Hi:
This isn't really a solution...just a clarification of what PROC REPORT is doing -- PROC REPORT isn't "setting variables" -- it's building report rows. This is more than a semantic difference. In a data step program, you have a buffer area called the Program Data Vector, which is always available and, in which, every time an observation is read from an input data set, the variable is placed in the PDV and is available for immediate reference (after the SET statement executes).

Proc Report, on the other hand, is NOT the data step. It takes the input dataset and does some preliminary processing based on your code. For example, if necessary, it might pass the data to the Means/Summary engine for summarizing or grouping. If you have an ACROSS usage in your program, then it assigns absolute column numbers to the report items, based on the number of levels of your across variable.

In addition, the compute block (in your case -- for my_util_pct) will not execute just ONE time for the PROC REPORT step, it will execute for every report row that is being built -- in order to populate your computed report item on every report row.

However, the %PUT -- any output from that statement is probably suspect. I'm actually surprised that you got anything at all from it. A regular PUT statement used in a COMPUTE block gives an error message that you are using the statement in the wrong context or something like that. I have never tried a %PUT because I always figured you'd get a similar error message. Good that you didn't, but I'm not surprised that it didn't give you the results you expected.

To help you understand what's happening, in general with PROC REPORT, run this code and examine the output from the PROC PRINT step and then compare the PROC PRINT output to the PROC REPORT report. It's not going to help with what you're trying to do (I don't understand the purpose of the %PUT and your DO LOOP anyway -- so perhaps you were trying to understand the compute block via your %PUT statments??) At any rate, there is a good section in the PROC REPORT doc called "How Proc Report Processes a Report" or some topic like that which might also help you figure out a better approach.

cynthia
[pre]
ods html file='c:\temp\show_across.html' style=sasweb;

proc report data=sashelp.prdsale nowd
out=work.acr_look;
where month between '01JAN94'd and '31MAR94'd;
column division month,(n actual predict newvar);
define division / group;
define month /across format=monyy5. order=internal;
define actual / sum;
define predict / sum;
define newvar / computed;
compute newvar;
_c5_ = _c4_ - _c3_;
_c9_ = _c8_ - _c7_;
_c13_ = _c12_ - _c11_;
endcomp;
rbreak after / summarize;
run;

proc print data=work.acr_look;
title 'what happens with across variables';
run;

ods html close;
[/pre]

[deleted_user]

Thanks for the information. I had not realized a macro was executed only once and was coding as if it was something that is used like a 'function'. This explains why I was seeing the results I was.

Thanks again for your help.

[Cynthia_sas]

Hi:
Well, here's a totally silly example that modifies the previous program. Basically, it's taking what would be IF logic in the compute block and moving it to a macro program,
%DOOTHR, that just builds an assignment statement.

I still have a hard time figuring out WHAT your macro was doing or supposed to do. It looked like you expected some date to change??? Were you trying to MAKE the across report columns/values based on a start and end date that were not in the data???

This paper has some good examples of PROC REPORT, including a particularly useful macro program to determine how many across values you're going to have.
http://support.sas.com/rnd/papers/sgf07/sgf2007-report.pdf

Otherwise perhaps you might consider contacting Tech Support with your data and your report requirement and see whether they can zero in on the best technique. If you run the program below, you will see that the %DOOTHR macro executes 1 time for every report row that is built. As I said, I was happy to find out that %PUT worked within the context of PROC REPORT -- but I don't find it very useful except for debugging, as shown in my modified program.

cynthia
[pre]
%macro doothr(div=);
%if &div = CONSUMER %then %do;
other = 99;
%put value of div = ÷
%end;
%else %if &div = EDUCATION %then %do;
other = _c5_ + _c9_ + _c13_;
%put value of div = ÷
%end;
%else %if &div = GTOT %then %do;
other = 9999;
%put value of div = ÷
%end;
%mend doothr;

ods html file='c:\temp\show_across.html' style=sasweb;

proc report data=sashelp.prdsale nowd
out=work.acr_look;
where month between '01JAN94'd and '31MAR94'd;
column division month,(n actual predict newvar) other;
define division / group;
define month /across format=monyy5. order=internal;
define actual / sum;
define predict / sum;
define newvar / computed;
define other / computed;
compute newvar;
_c5_ = _c4_ - _c3_;
_c9_ = _c8_ - _c7_;
_c13_ = _c12_ - _c11_;
endcomp;
compute other;
if division = 'CONSUMER' then do;
%doothr(div=CONSUMER);
end;
else if division = 'EDUCATION' then do;
%doothr(div=EDUCATION);
end;
else if _break_ = '_RBREAK_' then do;
%doothr(div=GTOT);
end;
endcomp;
rbreak after / summarize;
run;

ods html close;
[/pre]