BookmarkSubscribeRSS Feed
bilardi
Calcite | Level 5

PROC REPORT:

Does the order of the variables listed in the COLUMN statement and/or the order in which i use the define statement have any impack on compute blocks?


12 REPLIES 12
Reeza
Super User

Yes it has an impact.

From the docs:

PROC REPORT assigns values to the columns in a row of a report from left to right. Consequently, you cannot base the calculation of a computed variable on any variable that appears to its right in the report.

bilardi
Calcite | Level 5

Is there anyway to reorder the columns after all of the calculations and computations are complete

Reeza
Super User

No idea, but if the report is complicated I generally use a datastep to preprocess the data and then a proc report to display it.


Cynthia Zender has a great paper on creating complex reports in SAS that is worth a read.

Cynthia_sas
SAS Super FREQ

Thanks for the mention! Here's the paper link:

http://www2.sas.com/proceedings/forum2008/173-2008.pdf

(to see the zip file of programs, go to: http://support.sas.com/rnd/papers and look in the section for 2008 papers to find the zip file.

In the paper, Example 3, I think shows how to order the report ROWS (not columns) based on the summary values (such as descending order of total sales for each region), but it does mean making 2 passes thru the data.

However, the OP asked about reordering the COLUMNs -- which seems sort of strange to me. Once the left-to-right order of the items in a COLUMN statement has been set, you cannot change the order without making another pass through the data. So, for example, if you have this:

COLUMN NAME AGE SEX HEIGHT WEIGHT;

for your report, you cannot suddenly "reorder" within the same PROC REPORT step, so that you have NAME after AGE, for example. You could use PROC REPORT with OUT= to capture the results of grouping/summarizing and then use the OUT= dataset for a "final" report with the COLUMNS in a new order, but the original summarizing and break lines were probably based on a certain order to the original COLUMN statement, so I'm having a hard time visualizing what type of report would need to have the compute block, etc and then need to reply the data with the columns in a different order. I think that more info is really needed to give a more complete answer.

cynthia

bilardi
Calcite | Level 5

  Let say I have for example

COLUMN NAME SEX MAILED RESPONDED RESP_RATE

And i want a report that shows the response rate by sex, but I want the response rate to be the second column in my data.

Cynthia_sas
SAS Super FREQ

Hi:

I don't understand what you mean. Is RESP_RATE already in your data set or are you calculating it from MAILED and RESPONDED??? There is a HUGE difference between your DATA (in your SAS data set) and your REPORT (what is being created by PROC REPORT). And, it doesn't make sense, in the above COLUMN statement (COLUMN NAME SEX MAILED RESPONDED RESP_RATE;) for you to have NAME if what you want is a report summarized by SEX.

Because in your data you could have the NAMES in this order:

Alfred

Alice

Barney

Barbara

Carl

Charlie

Diane

David

in which case getting RESP_RATE by SEX will be hard (see report #1 in attached program).

I would expect the order in the COLUMN statement to be:

COLUMN SEX NAME MAILED RESPONDED RESP_RATE;

Using NOPRINT would allow you to make it appear as though your calculated variable was appearing -before- the items that were used to calculate it (see report #4 in the attached program). You seem to use the terms "data" and "report" interchangeably -- what does your program look like, how is your data structured? Is your data already summarized? What usages do you have for SEX, NAME, MAILED, RESPONDED and RESP_RATE in your PROC REPORT code?

You should be able to run the code below and perhaps it will point you in one direction or another to produce your REPORT (not a DATA set).

cynthia

ods listing close;

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

 

proc report data=sashelp.class nowd;

  title '1) Does Not Make sense to have NAME on the report';

  column name sex age height weight calcvar;

  define name / order;

  define sex / order;

  define age / mean;

  define height / mean;

  define weight / mean;

  define calcvar /computed;

  compute calcvar;

    calcvar = sum(height.mean, weight.mean);

  endcomp;

  break after sex / summarize;

  compute after sex;

    line ' ';

  endcomp;

run;

       

proc report data=sashelp.class nowd;

  title '2) Without Name Make More Sense';

  column  sex age height weight calcvar;

  define sex / group;

  define age / mean;

  define height / mean;

  define weight / mean;

  define calcvar /computed;

  compute calcvar;

    calcvar = sum(height.mean, weight.mean);

  endcomp;

  compute after sex;

    line ' ';

  endcomp;

run;

       

proc report data=sashelp.class nowd;

  title '3) Different Report with Sex and Age as GROUP vars';

  column  sex age height weight calcvar;

  define sex / group;

  define age / group;

  define height / mean;

  define weight / mean;

  define calcvar /computed;

  compute calcvar;

    calcvar = sum(height.mean, weight.mean);

  endcomp;

  break after sex / summarize;

  compute after sex;

    line ' ';

  endcomp;

run;

   

proc report data=sashelp.class nowd;

  title '4) Use NOPRINT to hide vars used in CALC';

  column  sex age height weight calcvar height=ht2 weight=wt2;

  define sex / group;

  define age / group;

  define height / mean noprint;

  define weight / mean noprint;

  define calcvar /computed;

  define ht2 / mean 'Height Alias';

  define wt2 / mean 'Weight Alias';

  compute calcvar;

    calcvar = sum(height.mean, weight.mean);

  endcomp;

  break after sex / summarize;

  compute after sex;

    line ' ';

  endcomp;

run;

ods _all_ close;

arunrami
Pyrite | Level 9

@Cynthia_sas 

 

 

Cynthia_sas
SAS Super FREQ

Hi, it depends on what you mean. Can you post some sample data and the code you've tried.

If all you want is for the first computed column to be created as a constant value, then this works for me:

appear_computed_first.png

Note that in Report #2, I use AGE (with NOPRINT) to determine the value of the computed variable. Technically in #2, the computed variable is NOT first on the COLUMN statement, but appears to be first on the report.
Cynthia

arunrami
Pyrite | Level 9

Hi @Cynthia_sas  , Thanks for the response. here is the code which I tried to to get computed variable to appear as second variable in report, but I am getting missing value(may be because the predefined logic of computed variable are treated as numeric). Please guide to achieve the expected result.

 

PROC REPORT DATA=work.Temp2 nowd HEADLINE HEADSKIP 
				style (report) = {background = white
				font_face = "Verdana" font_size = 7pt just=left bordercolor=grey rules=All frame=box}
				style (column) = {background = white CELLHEIGHT = 2.5%
				font_face = "Verdana" font_size = 7pt just=left }
				style (header) = {foreground = cx5e2750 font_face="Verdana"
				font_size = 8pt just=left
				};
		columns zp_fl_nm startingtime  start_time end_time duration ;
define zp_fl_nm/display ;
define startingtime/computed;
				define start_time/display 'Start_Time' format=datetime.;
	define end_time/display 'End_Time' format=datetime.;
				define duration/computed format=time.;
	compute startingtime/ character length=20;
				startingtime=catx('',put(datepart(start_time),DDMMYY10.),put(timepart(start_time),time.));
				endcomp;
run;
Cynthia_sas
SAS Super FREQ

Hi:

  You have a different issue. PROC REPORT has a "left to right" rule. When PROC REPORT builds a report row, it puts each variable value or computed value on the report row one item at a time, working from left to right. So, for example, here's a hypothetical scenario that illustrates the left to right concept:

column var1 var2 var3 var4 var5;

 

VAR1 cannot use any of the other variables in a COMPUTE block because at the point in time when VAR1 is placed on the report row, there is NO visibility of the values of any of the other variables.

In a COMPUTE block for VAR2 you can only reference VAR2 and VAR1 in that COMPUTE block.

In a COMPUTE block for VAR3 you can only reference VAR3, VAR2 and VAR1 in that COMPUTE block.

In a COMPUTE block for VAR4 you can only reference VAR4, VAR3, VAR2 and VAR1 in that COMPUTE block.

In a COMPUTE block for VAR5 you can only reference VAR5, VAR4, VAR3, VAR2 and VAR1 in that COMPUTE block.

 

  So your issue is that with this COLUMN statement is the left to right rule:

columns zp_fl_nm startingtime start_time end_time duration ;

in your COMPUTE block for STARTINGTIME, you can only reference zp_fl_nm. You CANNOT reference start_time, end_time, or duration.

 

  There have been a LOT of previous forum postings on the left to right rule with PROC REPORT. One way to fix your code is to put START_TIME and/or END_TIME before STARTINGTIME in the COLUMN statement. If you notice in my code for the COMPUTED column, in the one where I used AGE to calculate the value for my computed column, I had AGE used once, with NOPRINT, appearing before the computed column and then I used AGE a second time to put display in the location where I wanted it on the report. This is another feature of PROC REPORT where you can use a variable more than one time on the report. Very handy feature for situations like this.

 

Cynthia

arunrami
Pyrite | Level 9
Thaks @Cynthia_sas . First time I came to know about this left to right column logic in proc report . It works fine.

Please share the link here if you posted any detailed blog regarding proc report and proc template 🙂
Cynthia_sas
SAS Super FREQ
Hi,
There aren't any blog posts, but you should be able to search in the Forums. This Google search string worked for me:
site: communities.sas.com "left to right" PROC REPORT

Cynthia

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 12 replies
  • 5771 views
  • 2 likes
  • 4 in conversation