BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
shanky_44
Obsidian | Level 7

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20

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

View solution in original post

10 REPLIES 10
PaigeMiller
Diamond | Level 26

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

--
Paige Miller
shanky_44
Obsidian | Level 7

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
Tourmaline | Level 20

 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
Obsidian | Level 7

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
Tourmaline | Level 20

@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
Obsidian | Level 7

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
Tourmaline | Level 20

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!

PaigeMiller
Diamond | Level 26

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

--
Paige Miller

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 1044 views
  • 3 likes
  • 3 in conversation