topic Re: PROC REPORT multi avg in SAS Procedures

PROC REPORT multi avg

bustergomez — Sun, 21 Apr 2013 00:30:13 GMT

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);

DEFINE GRADE / F=$GRD. PRELOADFMT GROUP WIDTH=6 /*PRELOADED FMT STRUCTURE IN FORMAT FILE*/

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;

Re: PROC REPORT multi avg

allurai0412 — Sun, 21 Apr 2013 03:45:01 GMT

hi,

please check as

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;

Re: PROC REPORT multi avg

Ksharp — Sun, 21 Apr 2013 03:49:21 GMT

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

Re: PROC REPORT multi avg

bustergomez — Sun, 21 Apr 2013 16:43:58 GMT

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);

DEFINE GRADE / GROUP WIDTH=6

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;

Re: PROC REPORT multi avg

Cynthia_sas — Sun, 21 Apr 2013 17:16:41 GMT

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

Re: PROC REPORT multi avg

bustergomez — Mon, 22 Apr 2013 00:51:00 GMT

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;