Rollup totalling in SAS

omega1983 — Mon, 15 Jul 2013 03:44:58 GMT

proc sql;

create table test;

select payee_id, corp_payee_id, cash_adv_am,reason_cd

from adv_table

group by payee_id,corp_payee_id,reason_cd

quit;

sample output

payee_id corp_payee_id cashadv_am reason_cd

111 A_3 125 n

B_3 250 r

112 A_3 125 n

B_3 250 r

I want to execute a difference grouped as above so I want to add a total. So 125-250 should give a negative number by group. Here is the desired results:

payee_id corp_payee_id cashadv_am reason_cd difference

111 A_3 125 n

B_3 250 r

-125

112 A_3 125 n

B_3 250 r

-125

If easier the difference can be placed as a running total directly in the cashadv_am field. How would I get a rollup totalling to work. Should this be done using a having clause in the proc sql

Re: Rollup totalling in SAS

PGStats — Mon, 15 Jul 2013 04:19:48 GMT

Not sure I got this right. I assumed reason_cd determines the sign of the amount in the summation :

data adv_table;
input payee_id corp_payee_id $ cash_adv_am reason_cd $;
datalines;
111 A_3 125 n
111 B_3 250 r
112 A_3 125 n
112 B_3 250 r
;

proc sql;
create table rollup as
select
     payee_id,
     sum(
          case reason_cd
               when "r" then -cash_adv_am
               when "n" then cash_adv_am
               else 0
          end) as difference
from adv_table
group by payee_id;

select * from rollup;
quit;

topic Rollup totalling in SAS in SAS Programming

Rollup totalling in SAS

Re: Rollup totalling in SAS