topic Re: Howto group multiple variables and calculating mean withthe help of PROC SQL in SAS Programming

Howto group multiple variables and calculating mean withthe help of PROC SQL

shanky_44 — Mon, 11 Mar 2019 14:50:55 GMT

Hello everyone!

I have one data set with more than 30 variables. I am mentioning her only three of them for reference. I want to group SUBJID, TRT and mean value of VAlL which would be a new variable and have to keep other variables in data-set. i tried Proc sql but getting error while mentioning other variables. Imp point, I have to group SUBJID and TRT. Please help me out with this issue.

data have;

SUBJID TRT val mean_value

10001 Screening 2.3

10001 Screening 3.6

10001 Week24 2.3

10001 Week24 3.6

10002 Week24 3.6

10002 Screening 3.6

10002 Week24 2.3

10003 Screening 2.3

10003 Week24 2.3

10003 Week24 3.6

data want;

SUBJID TRT mean_value

10001 Screening 4.1

Week24 4.1

10002 Week24 4.1

Screening 4.1

10003 Screening 4.1

Week24 4.1

Re: Howto group multiple variables and calculating mean withthe help of PROC SQL

PaigeMiller — Mon, 11 Mar 2019 15:00:13 GMT

i tried Proc sql but getting error while mentioning other variables.

Show us the SAS Log (the entire PROC SQL part of the log, not just the errors). Click on the {i} icon and paste the Log into the window that appears DO NOT SKIP THIS STEP

Re: Howto group multiple variables and calculating mean withthe help of PROC SQL

novinosrin — Mon, 11 Mar 2019 15:06:20 GMT

1. And I have no clue how you got the mean_Value of 4.1

2. Your output rather looks like a report than a dataset???

Re: Howto group multiple variables and calculating mean withthe help of PROC SQL

shanky_44 — Mon, 11 Mar 2019 15:07:57 GMT

Hi PaigeMiller,

I am really sorry, I didn't have access to the same system where I was working. I posted here, to understand how to group multiple variables while doing calculation along keeping n number of variables.

Re: Howto group multiple variables and calculating mean withthe help of PROC SQL

PaigeMiller — Mon, 11 Mar 2019 15:09:04 GMT

PROC SUMMARY will do this calculating of the mean within groups, for many variables and different group variables.

Re: Howto group multiple variables and calculating mean withthe help of PROC SQL

shanky_44 — Mon, 11 Mar 2019 15:10:48 GMT

Please don't consider values as accurate calculation that part is just for reference. Basically I have to perform mean on val variable and create new variable keeping mean value. I have to keep this in a dataset.

Re: Howto group multiple variables and calculating mean withthe help of PROC SQL

shanky_44 — Mon, 11 Mar 2019 15:12:39 GMT

Thanks, I will try this.

Re: Howto group multiple variables and calculating mean withthe help of PROC SQL

novinosrin — Mon, 11 Mar 2019 15:13:15 GMT

@shanky_44 Are you after this?



data have;
input SUBJID        TRT     :$15.             val    ;*      mean_value;
cards;
10001        Screening            2.3
10001        Screening            3.6         
10001        Week24               2.3
10001         Week24              3.6
10002          Week24              3.6
10002          Screening           3.6
10002          Screening            3.6
10002          Week24              2.3
10003          Screening          2.3
10003          Screening          2.3
10003           Week24            2.3
10003          Week24             3.6
;

proc sql;
create table want as
select *,mean(val) as mean_value
from have
group by  SUBJID,TRT;
quit;

Re: Howto group multiple variables and calculating mean withthe help of PROC SQL

shanky_44 — Mon, 11 Mar 2019 15:34:46 GMT

I have one more question, if I have to keep n number of variables in data set. As In this example I have mentioned only 3 but I have more than 30 variables in my data set. How should I mention all variables. You can consider other variables a,b,c,d.Thanks!

Re: Howto group multiple variables and calculating mean withthe help of PROC SQL

novinosrin — Mon, 11 Mar 2019 15:36:52 GMT

Select *

The * asterisk will take care of that

In essence, it means select all columns (select *), calculate the mean, remerge the mean value to all obs in each by group

HTH & Regards!

Re: Howto group multiple variables and calculating mean withthe help of PROC SQL

shanky_44 — Mon, 11 Mar 2019 15:41:04 GMT

Thank you so much.