Adding Variables to Proc Report

[Scottcom4]

Hi All,

I am using the attached code to establish a ratio of sold to bought products.

What I want to do however is:

A) Control the order of the variables which are returned to me (product) so that they are no longer in alphabetical order.

B) Include an additional variable which is called "Total Fresh" and a variable called "Total Processed" so that products which are tinned or frozen are used to calculate the Total Processed ratio, whilst non-tinned and non-frozen products calculate the Total Fresh ratio (there is presently no variable to denote this).

Is anyone able to give me any advice on this?

options nodate pageno=1 linesize=64
pagesize=60;
proc report Data=Merged nowd Out = Frequency headline headskip;
column yr mth Product, (Sold Bought Frequency);
define Product / Across ' ';
define Yr / Group ' ' noprint;
define Mth / Group ' ';
define Bought / Analysis format = comma12.6 Sum noprint;
define Sold / Analysis format = comma12. Sum noprint;
define Ratio / Computed format = percent9.2 ' ';

Compute Ratio;

_c5_ = _c3_ /_c4_ ;
_c8_ = _c6_ /_c7_ ;
_c11_ = _c9_ /_c10_ ;
_c14_ = _c12_/_c13_ ;
_c17_ = _c15_/_c16_ ;
_c20_ = _c18_/_c19_ ;

Endcomp;

Compute mth;
IF _break_='_RBREAK_' THEN
CALL DEFINE('mth','style','style=[pretext="TOTAL"]');
Endcomp;

break after Yr / summarize skip;

run; Message was edited by: Scottcom4

[Cynthia_sas]

Hi:
I'm not sure I understand all of what you want to do. However, as far as getting the across variable in the order you want, look at this example:
http://support.sas.com/forums/thread.jspa?messageID=19625䲩
especially the use of the ACCORD variable in the example and the ORDER=DATA option of the DEFINE statement.

To help you with your problem description, generally, you refer to report items which come from your dataset as "variables" and then you refer to other items (such as COMPUTED usage items) as "report items". What is not clear to me is how the Total Fresh Ratio or the Total Processed Ratio report items should be calculated. They seem to be based only on the type of product??? You don't show a "type" variable in your COLUMN statement. Since you can control the order of the ACROSS variables, the answer could be as simple as using the different column numbers in different compute blocks (I made up these column numbers):

[pre]
compute Tot_Fresh;
tot_fresh = sum(_c3_ , _c6_) / sum(_c4_ , _c7_);
endcomp;
compute Tot_Proc;
tot_proc = sum(_c9_ , _c12_ , _c15_) / sum(_c10_ , _c13_ , _c16_);
endcomp;
[/pre]

This may not be what you had in mind, in which case, a more detailed example, perhaps with data, might help.

cynthia

[Scottcom4]

Hi Cynthia,

Thank you for your help. I have attached an example of what I am trying to do. I have used the sashelp.class dataset (although the meaures I am using make no sense, the outcomes should remain apparent). The only difference in what I am looking to achieve is that I have a variable called month which I have defined as a group variable, allowing me to see historical movement.

I have sorted the data so that all names associated with males appear after those relating to females. I now wish to add a variable between the start of the male names and the end of the female names called females and in that I wish to calculate the HWRatio of all the females. I wish to do the same for the males and have this figure presented following the final male name.

I hope that this better explains my desired outcomes. Once again I am very greatful for your assistance.

Proc Sort data = sashelp.class out = class;
by sex name;
Run;


proc report Data=class out = classreport nowd headline headskip;
column Name, (weight height HWRatio);
define Name / Across ' ' order = data;
define weight / Analysis format = comma12.6 Sum noprint;
define height / Analysis format = comma12.0 Sum noprint;
define HWRatio / Computed format = percent9.2 ' ';

Compute WHRatio;

_C3_ = _C2_/_C1_;
_C6_ = _C5_/_C4_;
_C9_ = _C8_/_C7_;
_C12_ = _C11_/_C10_;
_C15_ = _C14_/_C13_;
_C18_ = _C17_/_C16_;
_C21_ = _C20_/_C19_;
_C24_ = _C23_/_C22_;
_C27_ = _C26_/_C25_;
_C30_ = _C29_/_C28_;
_C33_ = _C32_/_C31_;
_C36_ = _C35_/_C34_;
_C39_ = _C38_/_C37_;
_C42_ = _C41_/_C40_;
_C45_ = _C44_/_C43_;
_C48_ = _C47_/_C46_;
_C51_ = _C50_/_C49_;
_C54_ = _C53_/_C52_;
_C57_ = _C56_/_C55_;

Endcomp;

run;

[Cynthia_sas]

Hi:
It seems to me that you are saying you want to add a new calculated report item as a new across item, but not under each name? Instead, after the female names, you want a ratio for females only and after the male names, you want to calculate a ratio for males only.

With your present ACROSS variables, you will have a hard time doing that. The only way that I can imagine doing it is for you to also use the SEX variable as an ACROSS variable on the report. For example, I have limited the CLASS data to only 6 observations -- 3 Females and 3 Males, for ease of illustration.
[pre]
proc sort data = sashelp.class out = class;
where name in ('Alfred', 'William', 'John','Alice','Barbara', 'Jane');
by sex name;
run;

options nodate nonumber nocenter ls=125;
title;
ods listing;

proc report Data=class out=classreport nowd headline headskip
nocompletecols;
column sex, (Name,(weight height HWRatio ) ('Gender Ratio' xxx));
define sex /across ' ';
define Name / Across ' ' order = data;
define weight / Analysis format = comma12.6 Sum noprint;
define height / Analysis format = comma12.0 Sum noprint;
define HWRatio / Computed format = percent9.2 ' ';
define xxx / computed ' ' f=percent9.2;
compute HWRatio;
_C3_ = _C2_/_C1_;
_C6_ = _C5_/_C4_;
_C9_ = _C8_/_C7_;
_c31_ = _c30_ / _c29_;
_C34_ = _C33_/ _C32_ ;
_C37_ = _C36_ /_C35_;
endcomp;
compute xxx;
_c19_= sum(_c2_,_c5_,_c8_) / sum(_c1_,_c4_,_c7_);
_c38_ = sum(_c30_,_c33_,_c36_)/sum(_c29_,_c32_,_c35_) ;
endcomp;

run;
[/pre]

The only complicating factor of this method is that the column numbers are set based on the total number of possible across (6) under each gender times the total number of columns under each name (6*3 =18). This means you need the NOCOMPLETECOLS to keep the unused across values from showing up under each gender value. This also means that even with 3 names for females, absolute column numbers are assigned to the 3 names for males, even if they're not going to be used, which means that column _C19_ will become the calculated gender ratio for females and _c38_ will become the calculated gender ratio for males.

cynthia

[Scottcom4]

Thanks Cynthia.

Is there a way to hide the sex variable from the output? I attempted to use noprint, but lost the entire output. Message was edited by: Scottcom4

[Cynthia_sas]

Hi:
The SEX variable as an ACROSS variable is what makes it possible for you to get your calculated ratios for Females/Males as separate columns. NOPRINT won't do what you want, as you discovered.

You could use a user-defined format to give the 'F' and 'M' different labels. Otherwise, your next choice is to presummarize and calculate the ratios yourself and then create the report.

cynthia