Total Scores with Matching Baseline help

Reply
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,

 

 

Grand Advisor
Posts: 9,571

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;
Respected Advisor
Posts: 4,606

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
Ask a Question
Discussion stats
  • 3 replies
  • 224 views
  • 0 likes
  • 4 in conversation