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
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.
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.