Proc Report Compute

Question

I am learning proc report and wanted to make a simple report with a computed column.

Here is my code :

proc report data = schools nowd;  
    columns school class maths science total;  
    define school       / group;  
    define class        / display;  
    define maths        / analysis;  
    define science      / analysis;  
    define total        / computed;  

    compute total;  
        total = maths + science;  
    endcomp;

run;

Here is the output which i am getting :

Schools   Class     Maths   Science      total  
Airport    i            50         41          0  
Airport    ii           92         53          0  
Airport    iii          62         60          0  
Airport    iv           66         61          0  
Amrut      i            84         58          0  
Amrut      ii           42         83          0  
Amrut      iii          53         64          0  
Amrut      iv           89        100          0  
Asia       i            42         74          0  
Asia       ii           48         91          0  
Asia       iii          75         76          0  
Asia       iv           46         84          0  

Can anyone please explain me why i am getting the value of total as 0. I believe it is possible to create a new column in PROC REPORT. What is it that i am doing wrong.

Thanks and Regards
Amit

Answer 1

Compound variable names are needed when an analysis variable has been used to calculate a statistic. You can reference maths and science as maths.sum and science.sum, respectively. If you had left those variables as display variables, you could also refer to them without compound names. The direct reference can be used c3 and c4, however, if you changed the order of those variables on the COLUMNS statement, it would alter your computation (just something to consider).

proc report data = schools nowd;  
 columns school class maths science total;  
 define school       / group;  
 define class        / display;  
 define maths        / analysis;  
 define science      / analysis;  
 define total        / computed;

 compute total;  
  total = maths.sum + science.sum;  
 endcomp;
run;

Answer 2

PROC REPORT compute order can be confusing. Basically, you have a log message saying 'Maths' and 'Science' are missing, because they haven't been associated with those columns as of the point in the report where the COMPUTE happens. You can use _C#_ where # is the column number to more easily refer to columns.

Also, as pointed out in comments, when accessing an analysis variable you need to refer to it by the type of analysis, so weight.sum instead of weight.

proc report data = sashelp.class nowd;  
    columns name sex height weight bmi;  
    define name / group;  
    define sex        / display;  
    define height / analysis;  
    define weight / analysis;  
    define bmi / computed;  

    compute bmi;  
        bmi=_c4_/(_c3_**2);
    endcomp;

run;

Answer 3

proc report data = school out= xyz nowd;  
    columns schools class maths science total;  
    define schools       / group;  
    define class        / display;  
    define maths        / order;  
    define science      / order;  
    define total        / computed; 

    compute total ;  
        if maths or science ne . then 
        total = maths + science ;  
    endcomp;

run;