BookmarkSubscribeRSS Feed
Ebba28
Calcite | Level 5

Hi everyone! I have a dataset which is divided into columns which has students divided by gender, by the school they go to, and then there a frequencies which describe the total number of each gender per school, together with the frequency of students who consume alcohol per gender, per school. I would somehow like to sum together the values that are in the 'tot' and 'yes' columns according to the genders 'boys' and 'girls' without accounting for the school these students go to. Below you will find the data set:

 

I would like a table that has simply the columns 'sum of yes' and sum of tot' according to the genders 'boys' and 'girls' but have no clue how to achieve this- would really appreciate the help!

The SAS System

 school   yes        tot        gender   percentage

12053boys0.37736
11461girls0.22951
21341boys0.31707
21125girls0.44000
31053boys0.18868
41454boys0.25926
4955girls0.16364
5541boys0.12195
51349girls0.26531
6940boys0.22500
61134girls0.32353
7736boys0.19444
7932girls0.28125
81855girls0.32727
91253boys0.22642
92164girls0.32813
101561boys0.24590
102349girls0.46939
111428boys0.50000
111628girls0.57143
12819boys0.42105
121225girls0.48000
131138boys0.28947
13624girls

0.25000

 

I would like a table that has simply the columns 'sum of yes' and sum of tot' according to the genders 'boys' and 'girls' but have no clue how to achieve this- would really appreciate the help! I would like it to look like this:

 

gender   yes                                           tot     

girls        (all cumulative yes from girls) (all cumulative tot from girls)

boys       (all cumulative yes from boys) (all cumulative tot from boys)

8 REPLIES 8
Amir
PROC Star

Hi @Ebba28 and welcome to the SAS Communities forum.

 

As you might have seen, the layout of your data in your post does not quite line up with your column headings. Please make this clearer by editing your original post as well as showing us what you want the output data to look like for the given input.

 

 

Thanks & kind regards,

Amir.

Ebba28
Calcite | Level 5

Hi Amir! Thank you for taking a look at this- have tried to arrange labels properly now and have also added an example of what I would like the output to look like! Any help would be super appreciated 🙂

Amir
PROC Star

@Ebba28, thanks for the confirmation.

 

In that case the code I have already posted looks like it should work.

 

Please respond either way.

 

 

Thanks & kind regards,

Amir.

chapidi99
Fluorite | Level 6

Hi,

 

Is it possible to provide a sample output so that it will be more clear how you would like to output.

 

Many thanks,

Kiran Chapidi

Ebba28
Calcite | Level 5

Hi Kiran! Thank you for taking your time to look at this- have added a piece on what I would like output to look like, in table format, hopefully it makes sense!

PaigeMiller
Diamond | Level 26

@Ebba28 wrote:

Hi everyone! I have a dataset which is divided into columns which has students divided by gender, by the school they go to, and then there a frequencies which describe the total number of each gender per school, together with the frequency of students who consume alcohol per gender, per school. I would somehow like to sum together the values that are in the 'tot' and 'yes' columns according to the genders 'boys' and 'girls' without accounting for the school these students go to. Below you will find the data set:

 

I would like a table that has simply the columns 'sum of yes' and sum of tot' according to the genders 'boys' and 'girls' but have no clue how to achieve this- would really appreciate the help!

 

This sounds like you want PROC MEANS or PROC SUMMARY with a CLASS statement, but it's really not clear exactly what you want and you ought to fix the data so that it's more readable (as suggested above) and show us the desired output. See if this example helps: https://documentation.sas.com/?docsetId=proc&docsetVersion=9.4&docsetTarget=p070bkysj4lkyun0zxj45n64...

--
Paige Miller
Ebba28
Calcite | Level 5

Hi Sienna! Have added an example of what I would like my output to look like in table format- perhaps you can help me with the suggestions you made? Thank you!

Amir
PROC Star

Assuming the input data is as follows, did you want something like below? If not then please provide further clarification.

 

data have;
   infile datalines dsd;

   input
      school  : $2.
      yes     :  8.
      tot     :  8.
      gender  : $5.
      percent :  8.
   ;

   datalines;
1,20,53,boys,0.37736
1,14,61,girls,0.22951
2,13,41,boys,0.31707
2,11,25,girls,0.44000
3,10,53,boys,0.18868
4,14,54,boys,0.25926
4,9,55,girls,0.16364
5,5,41,boys,0.12195
5,13,49,girls,0.26531
6,9,40,boys,0.22500
6,11,34,girls,0.32353
7,7,36,boys,0.19444
7,9,32,girls,0.28125
8,18,55,girls,0.32727
9,12,53,boys,0.22642
9,21,64,girls,0.32813
10,15,61,boys,0.24590
10,23,49,girls,0.46939
11,14,28,boys,0.50000
11,16,28,girls,0.57143
12,8,19,boys,0.42105
12,12,25,girls,0.48000
13,11,38,boys,0.28947
13,6,24,girls,0.25000
;

proc summary data = have nway;
   class gender;
   var yes tot;
   output out = want(drop = _:) sum=sum_of_yes sum_of_tot;
run;

 

 

Kind regards,

Amir.

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 8 replies
  • 792 views
  • 0 likes
  • 4 in conversation