topic Re: Edit values in columns in proc report in ODS and Base Reporting

Edit values in columns in proc report

Jennys — Mon, 14 Sep 2009 20:28:17 GMT

Hi

I have a proc report where I need to null some values based on a condition. It works fine when i have a "simple" proc report, but when I make a crosstable I have problems.
I have made an example of the two reports below:

In the first table the mean for sex=1 is set to 0, since there are fewer than 7 players.

I want the same for the second table, ie the only mean that should show is the mean for sex=2 and category=2. Since the number of categorys in the input table varies, I can´t use the _cx_ to fix this.

Any suggestions?

Tks in advance
Jenny

data test;
input sex category salary;
cards;
1 2 11000
1 2 10000
1 3 15000
1 3 16000
1 2 14500
1 2 13000
2 2 10000
2 3 10000
2 2 12000
2 2 13000
2 2 15000
2 2 17000
2 2 15000
2 2 17000

;
run;

ods listing close;
ods html body = 'test.html' style=seaside;
title 'This report works fine , with the mean nulled for less than 7 players';
proc report data = test nowd;
column sex salary=lant salary=lmean;
define sex / group "Sex" ;
define lant / n format = nlnum8. 'Players' ;
define lmean / mean format = nlnum8. 'Mean' style={just=r} ;
compute lmean;
if lant lt 7 then lmean = 0;
endcomp;

run;
title 'The columns for mean doesn´t get nulled';
proc report data = test out = tt nowd;
column sex category , ( salary=lant salary=lmean ) ;
define sex / group "Sex" ;
define category / across "" across;
define lant / n format = nlnum8. 'Players' ;
define lmean / mean format = nlnum8. 'Mean' style={just=r} ;

compute lmean;
if lant lt 7 then lmean = 0;
endcomp;

run;
ods html close;

Re: Edit values in columns in proc report

Cynthia_sas — Tue, 15 Sep 2009 01:30:20 GMT

Hi,
When you use PROC REPORT and an ACROSS usage to do cross-tabular reports, you need to use ABSOLUTE column names in your compute block.

Look at this previous forum posting for an explanation and some recommendations of how to do the coding and some recommendations of reading material on the subject.
http://support.sas.com/forums/thread.jspa?messageID=23870崾

cynthia

Re: Edit values in columns in proc report

Jennys — Tue, 22 Sep 2009 09:48:04 GMT

Hi

Thanks for your reply. I still have som issues about this. How do I refer to the column mean and n i this case? I want to clear mean if n is less than 5:

I also want to change the labels for mean and n, is that possible when you uses absolute column names with statistics?

proc report data = test nowd;
column sex category, salary, (mean n) ;
define sex / group "Sex" ;
define category / across "" across;

define salary / analysis ;
compute mean;
if n lt 5 then mean = 0;
endcomp;
run;
ods html close;

Thanks in advance
Jenny

Re: Edit values in columns in proc report

Cynthia_sas — Tue, 22 Sep 2009 14:27:35 GMT

Hi:
I'm not sure what you mean by changing the headers? You can use N and MEAN in DEFINE statements:
[pre]
define n / "Count of Players";
define mean / "Adjusted Mean";
[/pre]

This gives you the opportunity to change the column headers.

One useful technique for determining what absolute column names have been assigned to your ACROSS variables, is to use the OUT= option to take a look:
[pre]
proc report data=test nowd out=whatcols;
[/pre]

Then, a PROC PRINT after this step will reveal how the columns are named. In your case, if you had 2 possible values for category and you have N and MEAN nested underneath each category, then PROC REPORT would name 4 columns. Since SEX is the first column, the numbering would start at _c2_ and _c3_ for N and MEAN under the first category and _c4_ and _c5_ for N and MEAN under the second category.

You may wonder why I listed N first, that's because PROC REPORT builds a report row from Left to Right. If you want to conditionally alter the value for MEAN for every category, based on N, then N has to appear BEFORE MEAN in the COLUMN statement:
[pre]
column sex category, salary, (n mean);
[/pre]

If you want to test N when MEAN is being written on the report row (so you can change the MEAN), then N has to be placed on the report row BEFORE MEAN, so it is available for the test. PROC REPORT does not have a Program Data Vector (like the DATA step) -- so when N is being placed on the report, there is no visibility of MEAN.

So, finally, how you would change MEAN is in a COMPUTE block. You were on the right track -- but only for a report without an ACROSS variable. But the simple column name "MEAN" -- while it is OK for the COMPUTE block-- is not OK for the IF statement. You need to tell PROC REPORT exactly WHICH value of MEAN should be adjusted -- the value for the first category or the second category. This means that you will need more than one IF statement in your COMPUTE block. If you have 2 categories, then you will need 2 IF statements:
[pre]
compute mean;
if _c2_ lt 5 then _c3_ = 0;
if _c4_ lt 5 then _c5_ = 0;
endcomp;
[/pre]

Hope this helps clarify things,

cynthia

Re: Edit values in columns in proc report

Jennys — Wed, 23 Sep 2009 12:32:06 GMT

Hi

Thanks a lot. If I understand it right you have to use the _cx_ variables as references? So if I don´t know the number of across columns, I have to get that before the proc report.

I use the proc report in a webapplication, where the user can choose from several group variables.

Re: Edit values in columns in proc report

Cynthia_sas — Wed, 23 Sep 2009 14:21:48 GMT

Hi:
You're going to allow the user to select multiple ACROSS variables??? Or just one or two. In your example, you showed only category as the ACROSS variable, nested with Salary, N and Mean. The GROUP variables in PROC REPORT are what go down the rows -- so if the users select multiple GROUP variables, you would be OK.

Because the naming system follows a regular pattern, it is possible to write a SAS macro program to generate all the _cx_ statements you need as described in this paper on selected REPORT topics:
http://support.sas.com/rnd/papers/sgf07/sgf2007-report.pdf

If you are unfamiliar with the SAS Macro Facility, I recommend this paper as a good introduction:
http://www2.sas.com/proceedings/sugi28/056-28.pdf

cynthia