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


HI,

I have got this sort of data:

student ID    booksborrowed

  111             3

111               4

111               1

123                4

123                 1

124                  4

125                   2

Now I want to find the total i.e summary of books borrowed for each student ID for example.

student ID          Total booksborrowed   

111                        8

123                        5

124                         4

125                         2

Many thanks in advance.

1 ACCEPTED SOLUTION

Accepted Solutions
Haikuo
Onyx | Level 15

/*SQL*/

data have;

input student_ID  :$  booksborrowed ;

cards;

  111             3

111               4

111               1

123                4

123                 1

124                  4

125                   2

;

proc sql;

select student_id, sum(booksborrowed) as total from have group by student_id;

quit;

/*or data step*/

data want (drop=booksborrowed);

set have;

by student_id notsorted;

total+total*(-first.student_id)+booksborrowed;

if last.student_id then output;

run;

Haikuo

View solution in original post

3 REPLIES 3
Haikuo
Onyx | Level 15

/*SQL*/

data have;

input student_ID  :$  booksborrowed ;

cards;

  111             3

111               4

111               1

123                4

123                 1

124                  4

125                   2

;

proc sql;

select student_id, sum(booksborrowed) as total from have group by student_id;

quit;

/*or data step*/

data want (drop=booksborrowed);

set have;

by student_id notsorted;

total+total*(-first.student_id)+booksborrowed;

if last.student_id then output;

run;

Haikuo

Cynthia_sas
SAS Super FREQ

Hi:

  You did not say whether you wanted a REPORT (such as with TITLES and TOTALS, etc) or an output dataset of the summary information. In addition to the SQL and DATA step solutions already posted, SAS has several procedures that will create either reports or output datasets (or both) that would calculate total books borrowed. The procedures are: FREQ, TABULATE, REPORT, MEANS. See code below. Each procedure produces a report (shown in 1a, 2a, 3a and 4a output) and then each procedure also produces a dataset, which is printed with PROC PRINT (in 1b, 2b, 3b and 4b outputs).

cynthia

data have;

infile datalines;

input student_ID  $  booksborrowed ;

return;

datalines;

111             3

111             4

111             1

123             4

123             1

124             4

125             2

;

run;

   

ods listing close;

ods html file='c:\temp\use_procedures.html' style=sasweb;

 

proc freq data=have;

  title '1a) PROC FREQ method';

  tables student_ID / out=work.freqout;

  weight booksborrowed;

run;

    

proc print data=work.freqout;

  title '1b) dataset created by PROC FREQ';

run;

 

proc tabulate data=have f=3. out=work.tabout;

  title '2a) PROC TABULATE method';

  class student_ID;

  var booksborrowed;

  table student_ID all,

        booksborrowed*sum;

  keylabel sum = ' ';

run;

  

proc print data=work.tabout;

  title '2b) dataset created by PROC TABULATE';

run;

 

proc report data=have nowd out=work.repout;

  title '3a) PROC REPORT method';

  column student_id booksborrowed;

  define student_id / group;

  define booksborrowed / sum;

  rbreak after / summarize;

run;

  

proc print data=work.repout;

  title '3b) dataset created by PROC REPORT';

run;

  

proc means data=have sum;

  title '4a) PROC MEANS method';

  class student_id;

  var booksborrowed;

  output out=work.meansout /autoname;

run;

   

proc print data=work.tabout;

  title '4b) dataset created by PROC MEANS';

run;

   

ods html close;

title;

zetter
Calcite | Level 5

Thanks all for the answers and consideration.

Haiko was spot on, many thanks.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 3 replies
  • 691 views
  • 1 like
  • 3 in conversation