Creating new aggregated variables for base table

[AntrOsl]

Hi, 

I am pretty new to SAS, and are having some problems understanding how to create a base table. 

At the moment I have the following table:

Customer:      Month:       Variable1:          Variable2:                 .........   VariableN

1                         1                  56                      78

1                         2                  71                      81

1                         3                  70                      78

2                         1                  100                   102

2                         2                  98                      88

2                         3                  89                      81

What I want is this:

Customer:           Avg.Variable 1:                                                Change.Variable2:

1                  (mean of variable 1 for all months)                     (last month - first month) 

2                  (mean of variable 1 for all months)                     (last month - first month) 

Hope it is clear ish. Any suggestions? 

[s_lassen]

The means you could get using PROC MEANS or SQL. But when you also want last minus first for another variable, you may as well do the whole thing in a datastep, e.g.:

Data want;
  Sum_var1=0;
  Count_var1=0;
  do until(last.customer);
    set have;
    by customer;
    if not missing(Variable1) then do;
      Sum_var1=Sum_var1+Variable1;
      Count_var1=Count_var1+1;
      end;
    if first.customer then 
      First_var2=Variable2;
    end;
  Diff_var2=Variable2-First_var2; /* last value minus first value */
  if Count_var1 then
    Mean_var1=Sum_var1/Count_var1;
  keep customer Mean_var1 Diff_var2;
run;  

The data should be sorted by customer and month, of course.

It can also be done in SQL, though:

proc sql;
  create table want as select
    a.*,
    mean(a.Variable1) as mean_var1,
    Last.Variable2-First.variable2 as Diff_var2
  from
    have a,
    have first,
    have last
  where first.customer=a.customer
    and last.customer=a.customer
  group by a.customer
  having first.month=min(a.month)
     and last.month=max(a.month)
  order by a.customer, a.month
  ;
quit;

Here, you can easily get all the original rows without remerging with the original data afterwards. If you only want the two summary rows, you should add a DISTINCT after SELECT, replace a.* with a.customer, and drop a.month from the ORDER BY clause.