Total Scores with Matching Baseline help

Occasional Contributor
Posts: 6

Total Scores with Matching Baseline help

Hi all,

I have a scenario and I am not sure the best way to approach this problem with SAS.

I have a dataset which has the following

Subject         Visit          Test         Value
1                Baseline     BP01         20

1                Baseline     BP02         30

1                Baseline     BP03         30

1                Baseline     BP04         20

1                Week 2      BP01         20

1                Week 2       BP02         30

1                Week 2       BP04         20

1                Week 4       BP01         20

1                Week 4       BP02         30

1                Week 4       BP03         30

1                Week 4       BP04         10

1                Week 4       BP05         10

From this I want to create a Total Score so

Baseline = 20 + 30 + 30 + 20 = 80

For subsequent visits, I want to create a Total but only sum the tests that were available at Baseline, so for Week 2 for example, BP03 is missing, therefore no score will be calculated as all tests used for total have o have had a Baseline test.

Week 4, This has all tests, plus an extra test. I would only want to sum up the tests that are included at baseline so BP01 - BP04, so Week 4 would be 20 + 30 + 30 + 10 = 70.

The final dtatset form the above dataset would look as folows:

Subject         Visit          Test         Value
1                Baseline     Total         80

1                Week 4       Total         70

Any help with some code would be much appreciated. I am using SAS version 9.2.

Kind regards,

Super User
Posts: 10,766

Re: Total Scores with Matching Baseline help

OK. Assuming there are not missing and duplicated value of TEST in each group of Subject and Visit.

``````data have;
input Subject         Visit  & \$20.        Test   \$      value;
cards;
1                Baseline     BP01         20
1                Baseline     BP02         30
1                Baseline     BP03         30
1                Baseline     BP04         20
1                Week 2      BP01         20
1                Week 2       BP02         30
1                Week 2       BP04         20
1                Week 4       BP01         20
1                Week 4       BP02         30
1                Week 4       BP03         30
1                Week 4       BP04         10
1                Week 4       BP05         10
;
run;
data want;
array x{9999} \$ 20 _temporary_;
retain count;

do i=1 by 1 until(last.visit);
set have;
by Subject Visit;
if visit='Baseline' then do;x{i}=test;count=i;end;
end;
sum=0;n=0;
do until(last.visit);
set have;
by Subject Visit;
if visit='Baseline' then sum+value;
else if test in x then do;
n+1; sum+value;
end;
end;

if visit='Baseline' or n=count then do;
test='total'; output;
end;
drop i n count value;
run;
``````
New Contributor
Posts: 3

Re: Total Scores with Matching Baseline help

Assuming there aren't duplicated value of TEST in each group of Subject and Visit. I sugegest you use PROC TRANPOSE to transform the orginal dataset. It looks like this: PROC TRANSPOSE data=raw; by SUBJECT VISIT; var VALUE; id TEST; run; So you will get the transformed dataset look like whis: Subject Visit Test1 Test2 Test3 Test4 1 Baseline 20 30 30 20 1 Week 2 20 30 . 20 The you can sum the totoal scores much easier base on the transformed dataset . data want; set transformed; by subject visit; if first.subject then call missing(of flag1 flag2 flag3 flag4); if visit='Baseline' and ^missing(test1) then flag1='Y'; if visit='Baseline' and ^missing(test2) then flag2='Y'; if visit='Baseline' and ^missing(test3) then flag3='Y'; if visit='Baseline' and ^missing(test4) then flag4='Y'; array flag(*) flag1-flag4; array test(*) test1-test4; do i =1 to dim(test); if flag(i)='Y' and missing(test(i)) then test(i) = .; if flag(i) ne 'Y' and ^missing(test(i)) then test(i)=0; end; sum = test1+test2+test3+test4; run;
Esteemed Advisor
Posts: 5,521

Re: Total Scores with Matching Baseline help

Turns out to be a bit more difficult than it looks:

``````data have;
input Subject Visit &:\$10. Test :\$8. Value;
datalines;
1                Baseline     BP01         20
1                Baseline     BP02         30
1                Baseline     BP03         30
1                Baseline     BP04         20
1                Week 2       BP01         20
1                Week 2       BP02         30
1                Week 2       BP04         20
1                Week 4       BP01         20
1                Week 4       BP02         30
1                Week 4       BP03         30
1                Week 4       BP04         10
1                Week 4       BP05         10
;

proc sql;
create table want as
select
a.subject,
b.visit,
"Total" as test,
sum(c.value) as value
from
have as a inner join
(select unique subject, visit from have as b) on a.subject=b.subject left join
have as c on a.subject=c.subject and b.visit=c.visit and a.test=c.test
where a.visit="Baseline"
group by a.subject, b.visit
having nmiss(c.value) = 0;
select * from want;
quit;
``````
PG
Discussion stats
• 3 replies
• 273 views
• 0 likes
• 4 in conversation