Sum data in with unique identifier

[desireatem]

Hello Sir/Madam,

I have this longitudinal data that I wish to sum by each id, for example the data is;

Data DD;

Input id A;

Cards;

1     2

1    5

1    3

1     1 

2    2

2     1

2       3;

I want to create a new data by summing A by id such that the new data will be;

id   sum_A

1   11(2+5+3+1)

2   6

Please help.

Best,

DA

[Shmuel]

proc summary data=have;
   class ID;
   var a;
   output out=want sum=;
run;

[PeterClemmensen · Accepted solution]

data dd;
Input id A;
cards;
1 2
1 5
1 3
1 1
2 2
2 1
2 3
;

proc summary data=dd nway;
   class id;
   var A;
   output out=want(drop=_:) sum(A)=sum_A;
run;

[desireatem]

Thank you!

[s_lassen]

Always more than one way to do things in SAS. Apart from PROC SUMMARY, you can use a data step:

data want;
  do until(last.id);
    set dd;
    by id;
    sum_A=sum(sum_A,A);
    end;
  drop A;
run;

Or SQL:

proc sql;
  create table want as select
    id,
    sum(A) as sum_A
    from dd
    group by id
    ;
quit;

The first solution requires the data to be sorted, but may be faster if it already is sorted. But not necessarily; if SAS knows that the data is sorted (you used a PROC SORT or an SQL ORDER BY statement to create it), the two are probably equivalent. The same goes for PROC SUMMARY, where you can use BY ID instead of CLASS ID if the data is already sorted, which again may perform better if does not know that the data is sorted.

Normally, PROC SQL will deliver the data in the GROUP BY order, but this is not guaranteed behavior, so you may want to add an ORDER BY ID at the end of the SQL query, then it's explicit. 

[desireatem]

Thank you very much!

FA