CODEWITHSUNDEEP
sqlsas
smackersz88
smackersz88

Reputation: 93

Performing additional calculations in PROC SQL

I have this specification to derive AVAL for parameter Code DACAPNT

enter image description here

For the text highlighted in red this is the code I'm trying to use

*Performing calculations for DACAPNT AVAL *;
proc sql;
    create table sum_by_totda as
    select *
           ,sum(dastresn) as totn            
    from sdtm.da
    where datestcd in ("RETAMT","DISPAMT")
    group by usubjid, datestcd
    ;
quit;

My question is is there a way to do the calculation within this PROC SQL statement or would this require an additional data step/PROC SQL?

Upvotes: 1

Views: 87

Answers (1)

mjsqu
mjsqu

Reputation: 5417

You can include CASE/WHEN/THEN/ELSE/END logic within aggregate functions in SQL, so this could be achieved using something like this:

sum(case when paramcd = 'CAPSEXP' then aval else 0 end + 
case when datestcd = 'RETAMT' then dastresn else 0 end +
case when datestcd = 'DISPAMT' then -1*dastresn else 0 end)

Upvotes: 2

Related Questions