topic Re: sum over multiple records and retain other variables in SAS Programming

sum over multiple records and retain other variables

tinghlin — Thu, 03 Oct 2024 03:30:33 GMT

I have a data have 3 avariles:

ID AMOUNT year
8888 50 110
8889 1.10 110
8889 2.99 111
8890 3.48 110
8891 34.08 110
8891 144.87 110
8891 2.24 111
8892 0 110

how to sum over multiple records for amont and keep ID and most recnt year if the obervation has multiple records in SAS?

8888 50 110
8889 4.09 111
8890 3.48 110
8891 181.19 111
8892 0 110

Re: sum over multiple records and retain other variables

ballardw — Thu, 03 Oct 2024 04:32:25 GMT

Here's how to build and example data set and then summarize as requested for this data:

data have;
input ID AMOUNT year ;
datalines;
8888 50 110
8889 1.10 110
8889 2.99 111
8890 3.48 110
8891 34.08 110
8891 144.87 110
8891 2.24 111
8892 0 110
;

proc summary data=have nway;
   class id;
   var amount year;
   output out=want (drop=_:) sum(amount)=amount max(year)=year
   ;
run;

That assumes you want a data set.

A report could be:

Proc report data=have;
   columns id amount year;
   define id / group;
   define amount/ sum;
   define year /max;
run;

The options in Proc summary : Nway only the highest combination of types. Default is that summary will create multiple summaries combining different levels of Class variables.

The Drop= on the Output statement says to drop variables that start with _, which would be _type_ and _freq_.

Max with something like Year, the largest value, would likely be the "most recent" or "latest" value. If you had wanted the first that could be MIN. Other desires might require more complicated programming.

Re: sum over multiple records and retain other variables

JOL — Thu, 03 Oct 2024 14:43:40 GMT

Here's a Data Step Approach using Group Processing.

data table;
input ID AMOUNT year;
datalines;
8888 50 110
8889 1.10 110
8889 2.99 111
8890 3.48 110
8891 34.08 110
8891 144.87 110
8891 2.24 111
8892 0 110
;
run;

/* A prerequisite sort is needed in order to use Group Processing in the Data Step */
proc sort data=table out=table_s;
by id year;
run;

data table_sum;
set table_s;
by id year; /* This statement creates hidden variables in the PDV that
are used to control groups in this case
first.id, last.id, first.year, last.year */
if first.id then
total=0;
Total + amount;

if last.id and last.year;
drop amount;
run;