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