How to collapse in data step

[Therain]

Hi, I am a new SAS user and I use SAS 9.4. I have a data set like this:

id            numbers

1001            1

1001            1  

1001            1  

1001            1  

1002            1

1002            1

1003            1

1003            1

1003            1

...

I hope to collapse like this:

id                sum_numbers

1001             4

1002             2

1003             3

I searched some info and I used the following codes which works:

PROC MEANS DATA=have;
CLASS id;
VAR numbers;
OUTPUT OUT=have2 sum= ;
RUN;

However, it is not in the data set anymore. SAS gives me another seperate result table in a seperate window. So I am wondering if there is any magic code in the data step that can keep the results still as part of the dataset?

Thank you!

[Astounding]

The results that you described do not keep the sums in the data set.  You sketched out a separate data set.  What would you like the result to look like?

[Therain]

Hi, thanks for the reply! It could be a separate data set or in the same data set. Both are OK.

[Reeza]

SQL can. It essentially requires merging the original with the summary data step. 

This will generate a message in the log. You can do it in a data step but the code is more complex. 

Proc sql;

create table want as

select *, sum(numbers) as sum_numbers

from have

group by id;

quit. 

[Reeza · Accepted solution]

SQL can. It essentially requires merging the original with the summary data step. 

This will generate a message in the log. You can do it in a data step but the code is more complex. 

Proc sql;

create table want as

select *, sum(numbers) as sum_numbers

from have

group by id;

quit. 

[Therain]

It works!
Thank you very much!

[ctrlx]

data datas;
	input Id numbers;
datalines;
1001            1
1001            1
1001            .
1001            1
1002            1
1002            .
1003            1
1003            1
1003            1
run;
proc sort data=datas
 out=datas;
 by Id;
 run;
data sumbygroup (drop=numbers);
set datas;
by Id;
if first.Id then do; sum=numbers; count=1;end;
			else do; sum+numbers; count+1;end;
if last.Id then output;
run;

proc print data=sumbygroup;
run;

[Therain]

Thank you! It works as well!