SAS: Compute value of column under an ACROSS variable (Nested/Derived/Pseudo-Column)

Question

I can't seem to include a computed variable in a PROC REPORT. It works fine when the computed variable is a headline column, but when it forms part of an ACROSS group, I can't get it to work. I've only got so far as to be able to reference the columns direcly, which only gives me the result for a single ACROSS group, not both.

data have1;
   input username $  betdate : datetime. stake winnings winner;
   dateOnly = datepart(betdate) ;
   format betdate DATETIME.;
   format dateOnly ddmmyy8.;
   datalines; 
    player1 12NOV2008:12:04:01 90 -90 0
    player1 04NOV2008:09:03:44 100 40 1
    player2 07NOV2008:14:03:33 120 -120 0
    player1 05NOV2008:09:00:00 50 15 1
    player1 05NOV2008:09:05:00 30 5 1
    player1 05NOV2008:09:00:05 20 10 1
    player2 09NOV2008:10:05:10 10 -10 0
    player2 09NOV2008:10:05:40 15 -15 0
    player2 09NOV2008:10:05:45 15 -15 0
    player2 09NOV2008:10:05:45 15 45 1
    player2 15NOV2008:15:05:33 35 -35 0
    player1 15NOV2008:15:05:33 35 15 1
    player1 15NOV2008:15:05:33 35 15 1
run;

PROC PRINT; RUN;

Proc rank data=have1 ties=mean out=ranksout1 groups=2;
     var    stake winner;
     ranks  stakeRank winnerRank;
run;

PROC REPORT DATA=ranksout1 NOWINDOWS out=report;
    COLUMN stakerank winnerrank, (N stake=stakemean discountedstake);
    DEFINE stakerank / GROUP '' ORDER=INTERNAL;
    DEFINE winnerrank / ACROSS '' ORDER=INTERNAL;
    DEFINE stake / analysis sum noprint;
    DEFINE stakemean / analysis sum;
    DEFINE discountedstake / computed format=8.2 'discountedstake';
    COMPUTE discountedstake;
        _C4_ = _C3_ -1;
    ENDCOMP;
RUN;

I don't understand how a variable connected to an across group can be calculated. This only calculates the value of 'discountedstake' for column 'C4' and it doesn't make sense to do it again for column 7.

How can I include the value of that computed variable in each group?

Joe Joe · Accepted Answer · 2013-09-13T13:59:22

PROC REPORT DATA=ranksout1 NOWINDOWS out=report;
    COLUMN stakerank winnerrank, (N stake=stakemean discountedstake);
    DEFINE stakerank / GROUP '' ORDER=INTERNAL;
    DEFINE winnerrank / ACROSS '' ORDER=INTERNAL;
    DEFINE stake / analysis sum noprint;
    DEFINE stakemean / analysis sum;
    DEFINE discountedstake / computed format=8.2 'discountedstake';
    COMPUTE discountedstake;
    _C4_ = _C3_ -1;
    _C7_ = _C6_ -1;
    ENDCOMP;
RUN;

You just need to mention each column you want calculated. You might be able to do this with an array if you have many of them, or do it in a data step/view ahead of time.

SAS: Compute value of column under an ACROSS variable (Nested/Derived/Pseudo-Column)

1 Answers