Solved
Contributor
Posts: 40

# Calculate absolute value of difference of every possible pair of a set of variables.

Dear Community,

I have a data set that has the following columns:

ID     COL1     COL2     COL3     ...     COL7

*Update and clarification on 2014-08-22 at 11:12 am.

COL1 to COL7 are dates in the format

01JAN2014:00:00:00.000

I want to

1) calculate the absolute value of the difference between every possible pair of COL1, COL2, ..., COL7; there are 21 possible such pairs (7 choose 2 = 21)

2) store the 21 results from Step #1 in 21 new columns named COL1-2, COL1-3, ... to denote the absolute-valued difference between COL1 and COL2, between COL1 and COL3, ......

3) if any of these 21 differences are 6 months or less, then flag them as such in a new column.

Would you know how to do this in an automated way?

Thanks,

Eric

Accepted Solutions
Solution
‎08-22-2014 02:26 PM
Regular Contributor
Posts: 180

## Re: Calculate absolute value of difference of every possible pair of a set of variables.

This Macro does what you need. You can drop the variables you don't need:

%macro absolute;

data want;

set have;

%do i=1 %to 7;

%let k=%eval(&i+1);

%do j=&k %to 7;

col&i._&j = abs(col&i - col&j);

flag&i._&j = col&i._&j < '30JUN1960:00:00:00'dt;

%end;

%end;

run;

%mend absolute;

%absolute

All Replies
Super User
Posts: 13,542

## Re: Calculate absolute value of difference of every possible pair of a set of variables.

I just initialized the COL variables, you would likely use a SET statement and drop the base array bit with the ()

If you use this approach for more than 9 variables I would recommend naming the NEW variables with names like Col01_02 so the substr function could point to a consistent set of columns in the variable name.

data want;
array base Col1-Col7 (1 2 3 4 5  6 7);
array new Col1_2-Col1_7 Col2_3-Col2_7 Col3_4-Col3_7 Col4_5-Col4_7 Col5_6-Col5_7 Col6_7;
do I=1 to dim(new);
j=input(substr(vname(new),4,1),f1.);
k=input(substr(vname(new),6,1),f1.);
new= abs(base - base);
end;
run;

Super User
Posts: 23,724

## Re: Calculate absolute value of difference of every possible pair of a set of variables.

Flip the data and treat as re-admission problem.

Can cross join to itself using SQL to identify less than 6 month visits and to flag episodes as required.

Super User
Posts: 10,778

## Re: Calculate absolute value of difference of every possible pair of a set of variables.

What does your data look like ? Do these COL1 COL2 ...........  variable also contain the value of DATETIME  like variable DATE ?

" if any of these 21 differences are 6 months or less, then flag them as such in a new column."

if so, there is no need to make so many variables COL1-2, COL1-3, ...    ?

Contributor
Posts: 40

## Re: Calculate absolute value of difference of every possible pair of a set of variables.

Hi Ksharp,

Sorry for the confusion.  I have updated my post to clarify what the data look like.

Hope this helps.

Eric

Solution
‎08-22-2014 02:26 PM
Regular Contributor
Posts: 180

## Re: Calculate absolute value of difference of every possible pair of a set of variables.

This Macro does what you need. You can drop the variables you don't need:

%macro absolute;

data want;

set have;

%do i=1 %to 7;

%let k=%eval(&i+1);

%do j=&k %to 7;

col&i._&j = abs(col&i - col&j);

flag&i._&j = col&i._&j < '30JUN1960:00:00:00'dt;

%end;

%end;

run;

%mend absolute;

%absolute

Contributor
Posts: 40

## Re: Calculate absolute value of difference of every possible pair of a set of variables.

Thanks, CTorres!  This is very helpful!

Super User
Posts: 10,778

## Re: Calculate absolute value of difference of every possible pair of a set of variables.

OK. I think there are already some guys out there give you solution . Here is my version code .

```data have;
input id (col1-col7) (: datetime.);
format col1-col7 datetime.;
cards;
1 01JAN2014:00:00:00.000  01FEB2014:00:00:00.000 01APR2014:00:00:00.000 01AUG2014:00:00:00.000 01NOV2014:00:00:00.000 01DEC2014:00:00:00.000 01JAN2015:00:00:00.000
2 01JAN2013:00:00:00.000  01FEB2014:00:00:00.000 01APR2014:00:00:00.000 01AUG2014:00:00:00.000 01NOV2014:00:00:00.000 01DEC2014:00:00:00.000 01JAN2015:00:00:00.000
;
run;

data temp;
set have;
array x{*} col12-col17
col23-col27
col34-col37
col45-col47
col56-col57
col67
;
array c{*}      col1-col7 ;
k=0; flag=0;
do i=1 to dim(c)-1 ;
do j=i+1 to dim(c);
k+1;x{k}=abs(c{i}-c{j});
if x{k} le 6*30*24*60*60 then flag=1;
end;
end;
drop k i j;
run;

```

Xia Keshan

Contributor
Posts: 40

## Re: Calculate absolute value of difference of every possible pair of a set of variables.

Hi Ksharp!

I wish that I can mark your answer as correct, too - it worked as well!

Thank you!

Super User
Posts: 10,778

## Re: Calculate absolute value of difference of every possible pair of a set of variables.

Never mind. I have enough score .

Regular Contributor
Posts: 180

## Re: Calculate absolute value of difference of every possible pair of a set of variables.

Eric,

You can use the follwing macro to solve steps 1 and 2:

data have;
input col1 col2 col3 col4 col5 col6 col7;
cards;
1 2 3 4 5 6 7
1 3 5 7 9 11 13
;
run;

%macro absolute;
%let six=6;
%let seven=7;
data want;
set have;
%do i=1 %to &six;
%let k=%eval(&i+1);
%do j=&k %to &seven;
col&i._&j = abs(col&i - col&j);
%end;
%end;
run;
%mend absolute;
%absolute

For Step 3 you can create the flag variables using the following instruction just after calculating the col&i._&j variable: flag&i._&j = col&i._&j < &trigger;

&trigger should the value of six months in seconds: 6*30*24*60*60: %let trigger=15552000;

Regards,

Posts: 1,270

## Re: Calculate absolute value of difference of every possible pair of a set of variables.

If the ultimate goal is to create a flag variable then this may generate the desired results.

data want(drop=i j);

set have;

array col{*} col1-col7;

flag=0;

do i=1 to dim(col);

do j=i+1 to dim(col);

if abs(col{i}-col{j})<=6 then flag+1;

end;

end;

run;

🔒 This topic is solved and locked.