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