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 |
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.