Join table with case when

[Rahul_SAS]

Hello Experts,

I have two tables Acccount and Transaction and need create a new table that should have Account_ID, TotalCr, TotalDr and Balance.

Could you please help me to find the result.

data account;
input Account_ID $ Name $ Accout_Type $ DateOfOpening date9.;
infile datalines dlm=',';
format DateOfOpening date9.;
datalines;
001,Tom,A,01Jan2010
002,Paul,T,01Feb2010
003,Sandy,A,01Jan2010
004,Sam,A,01Dec2018
005,Nancy,T,01Nov2010
006,Harry,A,01Jan2010
007,Rosy,A,01Jul2010
008,Mac,T,01Sep2010
;
run;

data transaction;
input Account_ID $ Tr_Type $ Tr_Amount Date date9.;
infile datalines dlm=',';
format Date date9. Tr_Amount best32.;
datalines;
001,DR,1254,01Jan2010
002,CR,2546,01Feb2010
003,CR,5243,01Jan2010
004,DR,3654,01Dec2018
005,DR,7824,01Nov2010
006,CR,9587,01Jan2010
007,DR,6752,01Jul2010
008,CR,1578,01Sep2010
;
RUN;

[PaigeMiller]

In this case, CASE WHEN is not necessary, I don't see a use for it here; and in fact its usually better to explain the input data (which you did) and show the desired output data, without specifying any particular coding method.

I didn't produce a column for balance, as it seems this is an imaginary variable that doesn't exist.

proc sql;
    create table want as select
        a.account_id
        ,b.tr_type
        ,sum(b.tr_amount) as total
    from account as a left join transaction as b
        on a.account_id=b.account_id
    group by a.account_id,b.tr_type;
quit;

proc report data=want;
    columns account_id tr_type,total;
    define account_id/group;
    define tr_type/across;
    define total/sum;
run;