Given dataset1, how to create dataset2 containing the sums of similar dataset1 rows?

[sasmhe1]

I'm new to SAS, am starting to watch the beginner tutorials, and would appreciate advice on implementing this problem:

Given existing dataset1:

  USER   ACRES

  John       5

  John       7

  Tom        3

  Tom        4

  Tom        8

  (and more)

I'd like SAS to create dataset2, containing one record per USER containing total acres:

  USER    TOTAL_ACRES

  John             12

  Tom              15

  (and more)

No report, just a new dataset (dataset2).

Advice would be appreciated.  Or perhaps there's already a tutorial for this?       

[ballardw · Accepted solution]

One way to create data set.

proc summary data=dataset1 nway;
   class user;
   var acres;
   output out=dataset2 (drop=_:) sum=total_acres;
run;

[sasmhe1]

Thank you, ballardw.

I tried the code in SAS Studio, but got: Libref .. is not assigned.

I'll continue with the beginner tutorials to understand this message, and if I don't succeed, I'll return with another question!

Thank you again.

[ballardw]

Show the entire log of what you submitted.

Libref means you attempted to use a library that is not defined in your current session. There is no we would know anything about what libraries you have. Basically you would have had to use something like data= some.dataset1  The . tells SAS that SOME is supposed to be the name of a library, physical storage location for the dataset named Dataset1.

Normally a LIBNAME statement is provided to create a library reference (libref).

[sasmhe1]

Hi ballardw,

Your method worked (I got around the libref problem).

Now I need to understand why it worked (may take a while).

Thank you again.

P.S.  Thanks also to the other responses, as I'd like to learn those methods also.

[Reeza]

I highly recommend learning and understanding PROC MEANS. Pick one proc, learn it well, then learn other ways. PROC MEANS is incredibly versatile and can do many different summary statistics so it's a great place to start.

Here's a decent quick walkthrough of the features

https://sascrunch.com/proc-means/

[Kurt_Bremser]

While using PROC SUMMARY is the recommended way, here two other ways using SQL or a SORT/DATA step combination:

proc sql;
create dataset2 as
  select
    user,
    sum(acres) as total_acres
  from dataset1
  group by user
;
quit;

proc sort data=dataset1;
by user;
run;

data dataset2;
set dataset1;
by user;
if first.user
then total_acres = acres;
else total_acres + acres;
if last.user; /* subsetting IF, causes only one output per group */
keep user total_acres;
run;

The statement in the ELSE branch is a SUM Statement  and implies an automatic RETAIN of the summed variable.

The DATA step method is often needed when you need to do accumulations of values which cannot be handled with the summary functions of SQL or the statistics available in SUMMARY. Think concatenation of strings or determining presence of certain "vertical" value combinations.

The DATA step can also be done with a DO UNTIL loop:

data dataset2;
do until (last.user);
  set dataset1;
  by user;
  if first.user
  then total_acres = acres;
  else total_acres + acres;
end;
keep user total_acres;
run;