topic Re: Given dataset1, how to create dataset2 containing the sums similar dataset1 rows? in SAS Programming

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

sasmhe1 — Thu, 01 Sep 2022 01:44:15 GMT

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?

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

ballardw — Thu, 01 Sep 2022 01:02:21 GMT

One way to create data set.

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

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

sasmhe1 — Thu, 01 Sep 2022 01:29:00 GMT

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.

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

ballardw — Thu, 01 Sep 2022 03:37:41 GMT

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).

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

Kurt_Bremser — Thu, 01 Sep 2022 07:59:42 GMT

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;

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

sasmhe1 — Thu, 01 Sep 2022 18:48:29 GMT

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.

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

Reeza — Thu, 01 Sep 2022 18:56:43 GMT

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/