BookmarkSubscribeRSS Feed
omega1983
Calcite | Level 5


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

1 REPLY 1
PGStats
Opal | Level 21

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

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 1 reply
  • 1742 views
  • 0 likes
  • 2 in conversation