topic Re: Creating aggregated variable with arrays in SAS Programming

Creating aggregated variable with arrays

abmitch95 — Fri, 13 Jan 2017 14:30:49 GMT

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.

Re: Creating aggregated variable with arrays

Rick_SAS — Fri, 13 Jan 2017 14:50:56 GMT

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;

Re: Creating aggregated variable with arrays

RW9 — Fri, 13 Jan 2017 14:57:16 GMT

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;

Re: Creating aggregated variable with arrays

abmitch95 — Fri, 13 Jan 2017 15:58:21 GMT

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

Re: Creating aggregated variable with arrays

ballardw — Fri, 13 Jan 2017 16:03:24 GMT

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...