Summing content according to variables in one column

[Ebba28]

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

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

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)

[Amir]

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]

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]

@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]

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]

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]

---

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

[Ebba28]

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]

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.