Performing additional calculations in PROC SQL

[smackerz1988]

I have this specification to derive AVAL for parameter code DACAPNT

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?

[SASJedi · Accepted solution]

This can be accomplished using SQL in-line views:

proc sql;
    create table sum_by_totda as
    select a.usubjid
			 ,'DACAPNT' as PARAMCD
/*			 ,a.AVAL as Issued
			 ,Returned
			 ,Dispensed */
			 ,a.aval+Dispensed-Returned as AVAL
		from (
				select usubjid, AVAL
				        		 from sdtm.da  where paramcd ="CAPSEXP"
				) as a
	 	left join 
			  (
				select usubjid, sum(dastresn) as Returned
        		 from sdtm.da  where datestcd ="RETAMT"
			    group by usubjid
				) as b
		on a.usubjid=b.usubjid
	 	left join 
			  (
				select usubjid, sum(dastresn) as Dispensed
        			from sdtm.da  where datestcd ="DISPAMT"
			    	group by usubjid
				) as c
    	on a.usubjid=c.usubjid
	 	;
quit;

However, the calculation for AVAL specified doesn't make any sense to me. Here is what the values look like:

usubjid	PARAMCD	Issued	Returned	Dispensed	AVAL
1	DACAPNT	20	15	3	8
2	DACAPNT	20	15	3	8
3	DACAPNT	20	15	3	8
4	DACAPNT	20	15	1	6
5	DACAPNT	20	15	2	7

I would expect the final calculation to be AVAL-(Dispensed+Returned):

proc sql;
    create table sum_by_totda as
    select a.usubjid
			 ,'DACAPNT' as PARAMCD
             ,a.AVAL as Issued
             ,Returned
             ,Dispensed
			 ,a.aval-(Dispensed+Returned) as AVAL
		from (
				select usubjid, AVAL
				        		 from sdtm.da  where paramcd ="CAPSEXP"
				) as a
	 	left join 
			  (
				select usubjid, sum(dastresn) as Returned
        		 from sdtm.da  where datestcd ="RETAMT"
			    group by usubjid
				) as b
		on a.usubjid=b.usubjid
	 	left join 
			  (
				select usubjid, sum(dastresn) as Dispensed
        			from sdtm.da  where datestcd ="DISPAMT"
			    	group by usubjid
				) as c
    	on a.usubjid=c.usubjid
	 	;
quit;

Which yields:

usubjid	PARAMCD	Issued	Returned	Dispensed	AVAL
1	DACAPNT	20	15	3	2
2	DACAPNT	20	15	3	2
3	DACAPNT	20	15	3	2
4	DACAPNT	20	15	1	4
5	DACAPNT	20	15	2	3