mean of columns in dataset

[tonimitro]

Hello,

I recently just started learning SAS and I am struggling on what to do on this particular task.

So, i have 2 data sets. One data set has a list of columns and their values and the other dataset has a list of the column names from data set 1 that i need to find the mean.

The data sets look like this. This is just a sample data, but i have hundreds of columns in data set 1 and hundreds of rows in dataset2.

data testData1;
infile datalines delimiter=','; 
INPUT c1 c2 c3 c4 c5 c6 c7;
DATALINES;
13,5,4,6,78,12,4
65,45,78,78,12,45,12
66,66,65,65,66,88,66
11,10,4,5,6,7,5
11,10,4,5,6,7,20
; 

/*contains the list of comma separated column names from dataset1
Example: I need to compute the mean for columns c1, c2, c5 etc 
*/

data testData2;
infile datalines delimiter=',';
INPUT colNames;
DATALINES;
c1,c2,c5
c4,c5,c7
c4,c6,c7
c1,c2,c3
; 

I want to create a third dataset that will compute the mean of each of the rows in Dataset 1 based on each row on Dataset 2.

Output should look like this and it should have as many columns as there are rows in Dataset 2 and as many rows as there are rows in dataset 1. In this example 5 rows and 4 columns

Each of the rows in testData 2 should be a column in dataset3 and each of the rows should be the mean values from test data 1.

So for Row 1 in tesdata1, the mean of c1,c2,c5 is (13+5+78)/3 = 32, row 2 for the same columns is (65+45+12)/3=40.66 etc.

Same thing for the next set of columns c4, c5, c7   (6+78+4)/3 =5.6 for row 1, and for row 2 is (78+78+12)/3=56

Same thing for the next set of columns c4, c6, c7   (6+12+4)/3 =7.3 for row 1 and for row 2 is (78+45+12)/3=45

data testData1;
INPUT c1-c2-c5 c4-c5-c7 c4-c6-c7 c1-c2-c3;
DATALINES;
32   5.6  7.3
40.6  56  45
etc etc etc 
; 

I just needs some guidance to get started as there are many things that sas can do. Not sure if this is something easy to do with just a dataset, proc means, or just a macro.

I hope someone can help.

[art297]

You have five rows of values, but only 4 rows of column names. Which values did you want to use to calculate the mean for the fifth row?

Art, CEO, AnalystFinder.com

[art297]

If you were just missing the last row of column names, here is one way you can do what you want:

data testData1;
  infile datalines delimiter=','; 
  INPUT c1-c7;
  DATALINES;
13,5,4,6,78,12,4
65,45,78,78,12,45,12
66,66,65,65,66,88,66
11,10,4,5,6,7,5
11,10,4,5,6,7,20
; 

data testData2;
  infile datalines delimiter=',';
  INPUT colName1-colName3;
  DATALINES;
1,2,5
4,5,7
4,6,7
1,2,3
1,2,3
; 
data want ;*(drop=col:);
  set testData1;
  set testData2;
  array col(*) c1-c7;
  mean=mean(of col(colName1),col(colName2),col(colName3));
run;

Art, CEO, AnalystFinder.com

[Ksharp · Accepted solution]

It is very easy for IML code.

data testData1;
infile datalines delimiter=','; 
INPUT c1 c2 c3 c4 c5 c6 c7;
DATALINES;
13,5,4,6,78,12,4
65,45,78,78,12,45,12
66,66,65,65,66,88,66
11,10,4,5,6,7,5
11,10,4,5,6,7,20
; 


data testData2;
infile datalines delimiter=',';
INPUT colNames $80.;
c1=scan(colnames,1,',');
c2=scan(colnames,2,',');
c3=scan(colnames,3,',');
drop colnames;
DATALINES;
c1,c2,c5
c4,c5,c7
c4,c6,c7
c1,c2,c3
; 
proc iml;
use testdata1;
read all var _num_ into x[c=vname];
close;

use testdata2;
read all var _char_ into y;
close;

want=j(nrow(x),nrow(y),.);
do i=1 to nrow(y);
 col=y[i,];
 want[,i]=x[,col][,:];
end;

colname=translate(strip(compbl(rowcat(y))),'_',' ');

create want from want[c=colname];
append from want;
close;
quit;

proc print;run;

[tonimitro]

Thank you Ksharp. That is exaclty what i was looking for.