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
@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;
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
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.
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???
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.
@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;
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!
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!
Thank you so much.
PROC SUMMARY will do this calculating of the mean within groups, for many variables and different group variables.
Thanks, I will try this.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.