DATA Step, Macro, Functions and more

Rollup totalling in SAS

Reply
Contributor
Posts: 59

Rollup totalling in SAS


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

Respected Advisor
Posts: 4,649

Re: Rollup totalling in SAS

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;

PG

PG
Ask a Question
Discussion stats
  • 1 reply
  • 229 views
  • 0 likes
  • 2 in conversation