DATA Step, Macro, Functions and more

How to collapse in data step

Accepted Solution Solved
Reply
Contributor
Posts: 23
Accepted Solution

How to collapse in data step

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!


Accepted Solutions
Solution
‎07-13-2016 08:11 PM
Super User
Posts: 19,860

Re: How to collapse in data step

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. 

View solution in original post


All Replies
Super User
Posts: 5,516

Re: How to collapse in data step

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?

Contributor
Posts: 23

Re: How to collapse in data step

Posted in reply to Astounding
Hi, thanks for the reply! It could be a separate data set or in the same data set. Both are OK.
Super User
Posts: 19,860

Re: How to collapse in data step

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. 

Solution
‎07-13-2016 08:11 PM
Super User
Posts: 19,860

Re: How to collapse in data step

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. 

Contributor
Posts: 23

Re: How to collapse in data step

It works!
Thank you very much!
New Contributor
Posts: 3

Re: How to collapse in data step

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;
Contributor
Posts: 23

Re: How to collapse in data step

Thank you! It works as well!
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 913 views
  • 3 likes
  • 4 in conversation