BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
EricCai
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
CTorres
Quartz | Level 8

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

View solution in original post

11 REPLIES 11
ballardw
Super User

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
Super User

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
Super User

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
Calcite | Level 5

Hi Ksharp,

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

Hope this helps.

Eric

CTorres
Quartz | Level 8

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
Calcite | Level 5

Thanks, CTorres!  This is very helpful!

Ksharp
Super User

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
Calcite | Level 5

Hi Ksharp!

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

Thank you!

CTorres
Quartz | Level 8

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
Ammonite | Level 13

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 11 replies
  • 7621 views
  • 3 likes
  • 6 in conversation