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
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
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
end;
run;
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.
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, ... ?
Hi Ksharp,
Sorry for the confusion. I have updated my post to clarify what the data look like.
Hope this helps.
Eric
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
Thanks, CTorres! This is very helpful!
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
Hi Ksharp!
I wish that I can mark your answer as correct, too - it worked as well!
Thank you!
Never mind. I have enough score .
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,
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.