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
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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.