If I understand the problem statement correctly, you want to take a record, select all the other records within a certain range (+/- <some amount> from a given variable), and derive the mean across those columns. The hash object approach already listed will certainly give those results, and may perform better than my code below. I was going to suggest an approach such as: data want;
set have;
do i=1 to nobs;
set have (rename=(whatever=whatever2)) point=i nobs=nobs;
* your logic goes here ;
end;
run; but it occurred to me that this is really just manually creating a Cartesian product, i.e. crossing each observation with all other observations. Of course, SQL will create Cartesian products automatically for us. (As an aside, a Cartesian product is a concept, not just something limited to PROC SQL. SQL makes it easy since it automatically does it for us, but a Cartesian product isn't limited to PROC SQL. For example, the hash object approach can yield the same results, you just have to write the code to do so. You could also use an index key lookup as well.) You haven't commented on the size of your data; often a Cartesian product can be unwanted, or a mistake, and can result in a huge, potentially "runaway" table. But, if that isn't an issue for you, then perhaps you can modify the below code to meet your needs. Hope this helps... * full Cartesian ;
proc sql;
create table test1 as
select
a.Name as A_Name
,b.Name as B_Name
,a.Sex as A_Sex
,b.Sex as B_Sex
,a.Age as A_Age
,b.Age as B_Age
,a.Height as A_Height
,b.Height as B_Height
,a.Weight as A_Weight
,b.Weight as B_Weight
from
sashelp.class a
,sashelp.class b
;
quit;
* Cartesian within sex ;
proc sql;
create table test2 as
select
a.Name as A_Name
,b.Name as B_Name
,a.Sex as A_Sex
,b.Sex as B_Sex
,a.Age as A_Age
,b.Age as B_Age
,a.Height as A_Height
,b.Height as B_Height
,a.Weight as A_Weight
,b.Weight as B_Weight
from
sashelp.class a
,sashelp.class b
where
a.sex=b.sex
;
quit;
* Cartesian within sex and height +/- 2 ;
proc sql;
create table test3 as
select
a.Name as A_Name
,b.Name as B_Name
,a.Sex as A_Sex
,b.Sex as B_Sex
,a.Age as A_Age
,b.Age as B_Age
,a.Height as A_Height
,b.Height as B_Height
,a.Weight as A_Weight
,b.Weight as B_Weight
from
sashelp.class a
,sashelp.class b
where
a.sex=b.sex
and
b.height between a.height-2 and a.height+2
;
quit;
* And remove the match against yourself ;
proc sql;
create table test4 as
select
a.Name as A_Name
,b.Name as B_Name
,a.Sex as A_Sex
,b.Sex as B_Sex
,a.Age as A_Age
,b.Age as B_Age
,a.Height as A_Height
,b.Height as B_Height
,a.Weight as A_Weight
,b.Weight as B_Weight
from
sashelp.class a
,sashelp.class b
where
a.sex=b.sex
and
b.height between a.height-2 and a.height+2
and
a.Name ne b.Name
;
quit;
* This is closer to your example... ;
* You could also drop the height columns, they are just for debugging ;
proc sql;
create table test5 as
select
a.Name as A_Name
,a.Height as A_Height
,b.Height as B_Height
,a.Weight as A_Weight
,b.Weight as B_Weight
from
sashelp.class a
,sashelp.class b
where
b.height between a.height-2 and a.height+2
and
a.Name ne b.Name
;
quit;
* Now get the mean ;
proc summary data=test5 nway;
class A_Name;
var B_Weight;
output out=means mean= / autoname;
run;
... View more
