Creating aggregated variable with arrays

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

Creating aggregated variable with arrays

I am prepping a dataset for analysis that contains descriptive variables for each study participant, as well as the participant's last 5 partners. My goal is to create a variable that summarizes the degree of similarity between each participant and the last 5 partners for select characteristics (age, income, education, etc.). Here is the code I am currently working with, using age as an example:

 

data master2;
set master;

age_diff1 = partner1_age - age;
age_diff2 = partner2_age - age;
age_diff3 = partner3_age - age;
age_diff4 = partner4_age - age;
age_diff5 = partner5_age - age;

age_diff_agg = sum(age_diff1, age_diff2, age_diff3, age_diff4, age_diff5);

run;

 

Age difference is calculated for each of the last 5 partners, and then summed to create age_diff_agg (age difference aggregated). Is there a way to do this without having to write out the formula 5 times for each characteristic? The code above is giving me the result I need but it is tedious to repeat the process for 20+ variables. Thanks.


Accepted Solutions
Solution
‎01-13-2017 10:57 AM
Super User
Super User
Posts: 7,720

Re: Creating aggregated variable with arrays

[ Edited ]

Well, you have kind of shot yourself in the foot there.  Naming a variable partner1_age - i.e. not having the numeric on the end, makes arraying them harder.  You could try this which normalises the relevant data, then sums (no need to worry about how many or uneven elements then):

data have;
  pt=1; age=21; income=20000; partner1_age=34; partner2_age=32; partner3_age=30; partner1_income=12000; partner2_income=10000; partner3_income=24000; 
run;

proc transpose data=have out=t_have;
  by pt age income;
  var partner:;
run;

data want;
  set t_have;
  by pt;
  retain age_sum income_sum;
  if index(_name_,"age")>0 then age_sum=sum(age_sum,col1);
  if index(_name_,"income")>0 then income_sum=sum(income_sum,col1);
  if last.pt then output;
run;

Alternatively you could use arrays, but need to specify them:

/* Same have as above */
data want; set have; array ages{3} partner1_age partner2_age partner3_age; array incomes{3} partner1_income partner2_income partner3_income; age_sum=sum(of ages{*}); income_sum=sum(of incomes{*}); run;

But, that being said, if you just follow good naming conventions, your code simplifies so much:

data have;
pt=1; age=21; income=20000; partner_age1=34; partner_age2=32; partner_age3=30; partner_income1=12000; partner_income2=10000; partner_income3=24000;
run;

data want; set have; array partner_age{3}; array partner_income{3}; age_sum=sum(of partner_age{*}); income_sum=sum(of partner_income{*}); run;

View solution in original post


All Replies
SAS Super FREQ
Posts: 3,630

Re: Creating aggregated variable with arrays

You can use arrays.  You can either build an array of the age differences, or just sum the partner ages and subtract 5 times the age of the participant, like this:

 

data master2;
array age_diff [5] partner1_age-partner5_age;
set master;
age_diff_agg = sum(of age_diff[*]) - dim(age_diff)*age;
run;
Solution
‎01-13-2017 10:57 AM
Super User
Super User
Posts: 7,720

Re: Creating aggregated variable with arrays

[ Edited ]

Well, you have kind of shot yourself in the foot there.  Naming a variable partner1_age - i.e. not having the numeric on the end, makes arraying them harder.  You could try this which normalises the relevant data, then sums (no need to worry about how many or uneven elements then):

data have;
  pt=1; age=21; income=20000; partner1_age=34; partner2_age=32; partner3_age=30; partner1_income=12000; partner2_income=10000; partner3_income=24000; 
run;

proc transpose data=have out=t_have;
  by pt age income;
  var partner:;
run;

data want;
  set t_have;
  by pt;
  retain age_sum income_sum;
  if index(_name_,"age")>0 then age_sum=sum(age_sum,col1);
  if index(_name_,"income")>0 then income_sum=sum(income_sum,col1);
  if last.pt then output;
run;

Alternatively you could use arrays, but need to specify them:

/* Same have as above */
data want; set have; array ages{3} partner1_age partner2_age partner3_age; array incomes{3} partner1_income partner2_income partner3_income; age_sum=sum(of ages{*}); income_sum=sum(of incomes{*}); run;

But, that being said, if you just follow good naming conventions, your code simplifies so much:

data have;
pt=1; age=21; income=20000; partner_age1=34; partner_age2=32; partner_age3=30; partner_income1=12000; partner_income2=10000; partner_income3=24000;
run;

data want; set have; array partner_age{3}; array partner_income{3}; age_sum=sum(of partner_age{*}); income_sum=sum(of partner_income{*}); run;
New Contributor
Posts: 4

Re: Creating aggregated variable with arrays

Ah, did not realize including the numeric on the end was necessary. I'll rename the variables and use your second suggestion. Thanks!


Super User
Posts: 11,134

Re: Creating aggregated variable with arrays

Not required just easier to reference and process in order using SAS. You would see the the advantage very quickly with 100 variables: age_partner1- age_partner100 is ever so much easier to type...

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 131 views
  • 2 likes
  • 4 in conversation