topic Re: Summing content according to variables in one column in New SAS User

Summing content according to variables in one column

Ebba28 — Mon, 27 Jan 2020 14:39:32 GMT

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)

Re: Summing content according to variables in one column

Amir — Mon, 27 Jan 2020 11:40:11 GMT

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.

Re: Summing content according to variables in one column

chapidi99 — Mon, 27 Jan 2020 11:41:39 GMT

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

Re: Summing content according to variables in one column

PaigeMiller — Mon, 27 Jan 2020 12:12:17 GMT

@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=p070bkysj4lkyun0zxj45n643q1i.htm&locale=en

Re: Summing content according to variables in one column

Amir — Mon, 27 Jan 2020 12:31:17 GMT

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.

Re: Summing content according to variables in one column

Ebba28 — Mon, 27 Jan 2020 14:40:47 GMT

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 🙂

Re: Summing content according to variables in one column

Ebba28 — Mon, 27 Jan 2020 14:42:28 GMT

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!

Re: Summing content according to variables in one column

Ebba28 — Mon, 27 Jan 2020 14:43:16 GMT

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!

Re: Summing content according to variables in one column

Amir — Mon, 27 Jan 2020 14:48:32 GMT

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