Baseline mean by subject visits (SAS)

Question

I am attempting to calculate the baseline mean values at a specific visit. For example, if a subject does not have a specified visit, the baseline mean will be re-calculated omitting the subject.

Here is the data:

Subject            Visit              Value
001                Baseline           10
001                Visit 2            11
001                Visit 3            12
001                Visit 4            13
002                Baseline           11
002                Visit 2            12
002                Visit 4            13
002                Visit 5            14
003                Baseline           12
003                Visit 3            13
003                Visit 4            14
003                Visit 5            15

I'd like to obtain the following:

Visit             BaselineMean       VisitMean
Baseline          11                 11
Visit 2           10.5               11.5
Visit 3           11                 12.5
Visit 4           11                 13.3
Visit 5           11.5               14.5

Here is the table I have for the mean at each visit:

proc sql;
create table want as
select 
    visit, 
    mean(value) as meanValue
from have
group by visit;

Any insight would be greatly appreciated.

Answer 1

Consider a join of two aggregates, one of which uses a self join on itself:

proc sql;
   CREATE TABLE want as 
   SELECT bagg.Visit, bagg.BaselineMean, vagg.VisitMean
   FROM 
     (SELECT t2.Visit, MEAN(t1.Value) AS BaselineMean
      FROM have t1
      INNER JOIN have t2
         ON t1.Subject = t2.Subject 
        AND t1.Visit = 'Baseline'
      GROUP BY t2.Visit) bagg

   INNER JOIN
     (SELECT Visit, MEAN(Value) AS VisitMean
      FROM have 
      GROUP BY Visit) vagg
   ON bagg.Visit = vagg.Visit;
quit;

SQL Demo