PROC REPORT multi avg

Solved
Occasional Contributor
Posts: 9

PROC REPORT multi avg

Hi,

I'm trying to get averages for multiple columns, i.e.

A                                              B                                         C                                     D                                    EVERYONE ELSE              US(SUM OF A,B,C,D)

AUTH  ASGN     AVG           AUTH  ASGN     AVG          AUTH  ASGN     AVG          AUTH  ASGN    AVG                     AUTH  ASGN    AVG               AUTH  ASGN    AVG

store1         5          5        100%             3        0             0               2         3          66%            1          1         100%                     20      30        66%                 11       9         122%

store2     .............

I have coded this: however when I include AVG to (AUTH ASGN AVG) I get AUTH and ASGN uninitialized error and cant divide by zero and my all my AVG have .

Any inputs would be helpful, thanks.

PROC REPORT /*FIRST PROC REPORT*/

DATA=AUTHASGN.TEMP ;

COLUMN GRADE AFSC_3D BASE, (AUTH ASGN AVG);

ORDER=DATA;

DEFINE AFSC_3D / GROUP WIDTH=9;

DEFINE BASE / ACROSS;

DEFINE AUTH / ANALYSIS;

DEFINE ASGN / ANALYSIS;

DEFINE AVG / COMPUTED FORMAT=PERCENT8.2;

*-------CALCULATIONS FOR EACH ROW-----------*;

COMPUTE AVG;

/*IF AUTH=0 THEN AVG=ASGN/1;*/

AVG=(AUTH/ASGN);

ENDCOMP;

/*BREAK AFTER GRADE / SKIP SUMMARIZE DOL DUL;*/

RUN;

Accepted Solutions
Solution
‎04-21-2013 01:16 PM
SAS Super FREQ
Posts: 9,370

Re: PROC REPORT multi avg

Hi,

As several people have indicated, the simple formula AVG=(ASGN/AUTH) that you use is incorrect -- it would ONLY ever work if ASGN and AUTH were 1) DISPLAY items and 2) not being used with an ACROSS item and 3) you NEVER wanted to use them as ANALYSIS items.

IF the usage for ASGN and AUTH were ANALYSIS MEAN, then as long as you did NOT have them under an ACROSS item, the revised formula:

AVG=ASGN.MEAN/AUTH.MEAN

would be OK.

However with ACROSS items on your report you must, must, must use absolute column numbers in your COMPUTE block. There have been many (MANY) previous forum postings about using absolute column numbers to perform calculates with PROC REPORT. Ksharp has indicated one way for you to review which absolute column numbers will get created for the variables that are nested underneath the ACROSS variable(s).

Merely reposting the same (non-working) code shows that you have not done any reading of the PROC REPORT documentation (such as the documentation topic on how PROC REPORT builds a report) and also shows that you have not even searched for previous forum postings on the topic.

For example, searching in the forum on the string

across absolute column

reveals many previous forum postings on the topic. Here are 5 to get you started. There are more, many more. If you want to find more, you can search using the search box in the upper right hand box on every forum screen:

https://communities.sas.com/message/154900#154900

https://communities.sas.com/message/153002#153002

https://communities.sas.com/message/112134#112134

https://communities.sas.com/message/49987#49987

https://communities.sas.com/message/50996#50996

Do note that the reference syntax for absolute column numbers is _c2_ and _c3_, etc. In some of the older posts from the previous system, the underscores mysteriously disappeared from the posts...so _c3_ might show up as just c3 -- be aware that if you use those older program, you might have to correct the references. The absolute column number is ALWAYS underscore-Cn-underscore:

_c7_=_c5_/_c6_; or

call define ('_c3_','style','style={background=lightyellow}');

cynthia

All Replies
Frequent Contributor
Posts: 97

Re: PROC REPORT multi avg

hi,

DEFINE AUTH / ANALYSIS mean;

DEFINE ASGN / ANALYSIS mean;....

.........

.........

COMPUTE AVG;

/*IF AUTH=0 THEN AVG=ASGN/1;*/

AVG=(AUTH.mean/ASGN.mean);

ENDCOMP;

or  please check the example with SASHELP.CLASS...

Proc Report data=sashelp.class nowd;
columns name sex age height weight ratio;
define name / 'name' display width=10;
define sex / display  width=6;
define age / display  width=4;
define height /'htmean' analysis mean  format=8.1;
define weight /'wtmean' analysis mean  format=8.1;
define ratio / computed format=percent8.2;

compute ratio;
ratio = height.mean / weight.mean;
endcomp;
rbreak after / summarize;
run;

Super User
Posts: 10,784

Re: PROC REPORT multi avg

AUTH ASGN these two columns name have been changed. Use out= option(which generate a dataset ) to check what real names they are .

PROC REPORT out=checkme

Ksharp

Occasional Contributor
Posts: 9

Re: PROC REPORT multi avg

Perhaps I should give a better example.  Here's an example of my data and code:

As for AUTH and ASGN, I'm looking for the actual count, not the mean.  The ratio between the two AUTH and ASGN would be the %.

PROC REPORT /*FIRST PROC REPORT*/

DATA=AUTHASGN.TEMP2 OUT=CHECKME; /*FOR TESTING MISSING IS OMITTED*/

COLUMN GRADE AFSC_3D BASE, (AUTH ASGN AVG);

ORDER=DATA;

DEFINE AFSC_3D / GROUP WIDTH=9;

DEFINE BASE / ACROSS;

DEFINE AUTH / SUM;

DEFINE ASGN / SUM;

DEFINE AVG / COMPUTED;

*-------CALCULATIONS FOR EACH ROW-----------*;

COMPUTE AVG;

/*IF AUTH=0 THEN AVG=ASGN/1;*/

AVG=(ASGN/AUTH);

ENDCOMP;

RUN;

Solution
‎04-21-2013 01:16 PM
SAS Super FREQ
Posts: 9,370

Re: PROC REPORT multi avg

Hi,

As several people have indicated, the simple formula AVG=(ASGN/AUTH) that you use is incorrect -- it would ONLY ever work if ASGN and AUTH were 1) DISPLAY items and 2) not being used with an ACROSS item and 3) you NEVER wanted to use them as ANALYSIS items.

IF the usage for ASGN and AUTH were ANALYSIS MEAN, then as long as you did NOT have them under an ACROSS item, the revised formula:

AVG=ASGN.MEAN/AUTH.MEAN

would be OK.

However with ACROSS items on your report you must, must, must use absolute column numbers in your COMPUTE block. There have been many (MANY) previous forum postings about using absolute column numbers to perform calculates with PROC REPORT. Ksharp has indicated one way for you to review which absolute column numbers will get created for the variables that are nested underneath the ACROSS variable(s).

Merely reposting the same (non-working) code shows that you have not done any reading of the PROC REPORT documentation (such as the documentation topic on how PROC REPORT builds a report) and also shows that you have not even searched for previous forum postings on the topic.

For example, searching in the forum on the string

across absolute column

reveals many previous forum postings on the topic. Here are 5 to get you started. There are more, many more. If you want to find more, you can search using the search box in the upper right hand box on every forum screen:

https://communities.sas.com/message/154900#154900

https://communities.sas.com/message/153002#153002

https://communities.sas.com/message/112134#112134

https://communities.sas.com/message/49987#49987

https://communities.sas.com/message/50996#50996

Do note that the reference syntax for absolute column numbers is _c2_ and _c3_, etc. In some of the older posts from the previous system, the underscores mysteriously disappeared from the posts...so _c3_ might show up as just c3 -- be aware that if you use those older program, you might have to correct the references. The absolute column number is ALWAYS underscore-Cn-underscore:

_c7_=_c5_/_c6_; or

call define ('_c3_','style','style={background=lightyellow}');

cynthia

Occasional Contributor
Posts: 9

Re: PROC REPORT multi avg

Cynthia,

Thanks for you help.  I'm pretty new at proc report.  I used the program below to understand it a little better.  Its hard was difficult for me to understand the other folks because I didn't really understand how to ask the question.  As for reading, I really couldn't find anything with my similar needs due to the re-posting.

data test;

input state \$11. city \$9. area \$2. people 2. earning 3.;

datalines;

PUNJAB     NABHA    A 20 14

PUNJAB     NABHA    B 10 5

PUNJAB     NABHA    C 5  2

PUNJAB     NABHA    A 41 26

PUNJAB     NABHA    B 15 10

PUNJAB     NABHA    C 14 6

PUNJAB     NABHA    A 52 24

PUNJAB     NABHA    B 35 32

PUNJAB     NABHA    C 42 40

PUNJAB     NABHA    A 74 50

PUNJAB     NABHA    B 25 12

PUNJAB     NABHA    C 42 30

PUNJAB     PATIALA  A 21 15

PUNJAB     PATIALA  B 11 5

PUNJAB     PATIALA  C 6  3

PUNJAB     PATIALA  A 42 30

PUNJAB     PATIALA  B 16 10

PUNJAB     PATIALA  C 15 10

PUNJAB     PATIALA  A 53 41

PUNJAB     PATIALA  B 36 32

PUNJAB     PATIALA  C 43 14

MAHARSHTRA MUMBAI   A 75 70

MAHARSHTRA MUMBAI   B 21 15

MAHARSHTRA MUMBAI   C 11 8

MAHARSHTRA MUMBAI   A 6  3

MAHARSHTRA MUMBAI   B 42 30

MAHARSHTRA MUMBAI   C 16 10

MAHARSHTRA KHANDALA A 15 10

MAHARSHTRA KHANDALA B 53 50

MAHARSHTRA KHANDALA C 36 30

MAHARSHTRA KHANDALA A 43 40

MAHARSHTRA KHANDALA B 75 70

MAHARSHTRA KHANDALA C 26 20

MAHARSHTRA KHANDALA A 43 40

MAHARSHTRA KHANDALA B 22 20

MAHARSHTRA KHANDALA C 12 10

MAHARSHTRA KHANDALA A 7  3

MAHARSHTRA KHANDALA B 43 40

MAHARSHTRA KHANDALA C 17 16

MAHARSHTRA KHANDALA A 16 15

MAHARSHTRA KHANDALA B 54 50

MAHARSHTRA KHANDALA C 37 36

;

RUN;

PROC REPORT DATA = test nowd OUT=CHECKME11;

COLUMN STATE CITY AREA,(PEOPLE EARNING EARNING_PER);

DEFINE STATE/GROUP;

DEFINE CITY/GROUP;

DEFINE AREA/ACROSS;

DEFINE PEOPLE/sum;

DEFINE EARNING/sum;

DEFINE EARNING_PER/computed f=PERCENT10.2 'Earning Per';

BREAK AFTER STATE/SUMMARIZE;

RBREAK AFTER/SUMMARIZE;

compute earning_per;

_c5_ = _c4_ / _c3_;

_c8_ = _c7_ / _c6_;

_c11_ = _c10_ / _c9_;

endcomp;

RUN;

🔒 This topic is solved and locked.