topic Re: Sum data in with unique identifier in SAS Programming

Sum data in with unique identifier

desireatem — Mon, 31 Aug 2020 04:04:04 GMT

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,

Re: Sum data in with unique identifier

Shmuel — Mon, 31 Aug 2020 04:29:52 GMT

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

Re: Sum data in with unique identifier

PeterClemmensen — Mon, 31 Aug 2020 05:29:31 GMT

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;

Re: Sum data in with unique identifier

s_lassen — Mon, 31 Aug 2020 11:04:45 GMT

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.

Re: Sum data in with unique identifier

desireatem — Mon, 31 Aug 2020 12:55:51 GMT

Thank you!

Re: Sum data in with unique identifier

desireatem — Mon, 31 Aug 2020 12:56:30 GMT

Thank you very much!