BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Therain
Calcite | Level 5

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

7 REPLIES 7
Astounding
PROC Star

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
Calcite | Level 5
Hi, thanks for the reply! It could be a separate data set or in the same data set. Both are OK.
Reeza
Super User

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
Super User

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
Calcite | Level 5
It works!
Thank you very much!
ctrlx
Fluorite | Level 6
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
Calcite | Level 5
Thank you! It works as well!

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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