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

[EricCai]

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

[ballardw]

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;

[Reeza]

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.

[Ksharp]

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

[EricCai]

Hi Ksharp,

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

Hope this helps.

Eric

[CTorres · Accepted solution]

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

[EricCai]

Thanks, CTorres!  This is very helpful!

[Ksharp]

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

[EricCai]

Hi Ksharp!

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

Thank you!

[Ksharp]

Never mind. I have enough score .

[CTorres]

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,

[stat_sas]

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;