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
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.