topic Re: How to collapse in data step in SAS Programming

How to collapse in data step

Therain — Wed, 13 Jul 2016 23:22:46 GMT

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

id numbers

1001 1

1002 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!

Re: How to collapse in data step

Astounding — Wed, 13 Jul 2016 23:35:49 GMT

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?

Re: How to collapse in data step

Reeza — Wed, 13 Jul 2016 23:39:24 GMT

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.

Re: How to collapse in data step

Reeza — Wed, 13 Jul 2016 23:39:24 GMT

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.

Re: How to collapse in data step

Therain — Thu, 14 Jul 2016 00:06:29 GMT

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

Re: How to collapse in data step

Therain — Thu, 14 Jul 2016 00:11:03 GMT

It works!
Thank you very much!

Re: How to collapse in data step

ctrlx — Thu, 14 Jul 2016 14:07:11 GMT

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;

Re: How to collapse in data step

Therain — Thu, 14 Jul 2016 17:38:05 GMT

Thank you! It works as well!