BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
bustergomez
Calcite | Level 5

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;

1 ACCEPTED SOLUTION

Accepted Solutions
Cynthia_sas
SAS Super FREQ

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

View solution in original post

5 REPLIES 5
allurai0412
Fluorite | Level 6

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;

Ksharp
Super User

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

bustergomez
Calcite | Level 5

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;

Cynthia_sas
SAS Super FREQ

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

bustergomez
Calcite | Level 5

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;

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 5 replies
  • 1117 views
  • 7 likes
  • 4 in conversation