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

[shanky_44]

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          Screening            3.6

10002          Week24              2.3

10003          Screening          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

[PaigeMiller]

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

[shanky_44]

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. 

[novinosrin]

 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???

[shanky_44]

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. 

[novinosrin · Accepted solution]

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

[shanky_44]

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!

[novinosrin]

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!

[shanky_44]

Thank you so much. 

[PaigeMiller]

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

[shanky_44]

Thanks, I will try this.