topic Re: Calculate absolute value of difference of every possible pair of a set of variables. in SAS Programming

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

EricCai — Thu, 21 Aug 2014 21:18:03 GMT

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

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

ballardw — Thu, 21 Aug 2014 21:51:01 GMT

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;

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

Reeza — Thu, 21 Aug 2014 22:02:53 GMT

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.

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

Ksharp — Fri, 22 Aug 2014 13:21:27 GMT

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

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

CTorres — Fri, 22 Aug 2014 15:17:19 GMT

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,

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

stat_sas — Fri, 22 Aug 2014 17:35:40 GMT

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;

run;

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

EricCai — Fri, 22 Aug 2014 18:16:17 GMT

Hi Ksharp,

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

Hope this helps.

Eric

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

CTorres — Fri, 22 Aug 2014 18:26:27 GMT

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;

run;

%mend absolute;

%absolute

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

Ksharp — Sat, 23 Aug 2014 14:05:32 GMT

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

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

EricCai — Mon, 25 Aug 2014 22:14:25 GMT

Thanks, CTorres! This is very helpful!

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

EricCai — Mon, 25 Aug 2014 22:15:28 GMT

Hi Ksharp!

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

Thank you!

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

Ksharp — Tue, 26 Aug 2014 12:06:20 GMT

Never mind. I have enough score .