Hi, Try this code. This is based on your requirements from your initial post. Note that this is based on the following assumption:
- A '
/* extracting the numeric portion out of study_day_ip */
proc sql;
create table tmp_want1 as
select a.*,
case
when Study_day_IP eq '' then ''
when anyalpha(substr(Study_day_IP,length(Study_day_IP)-2, 3)) = 0 then compress(substr(Study_day_IP,length(Study_day_IP)-2, 3))
when anyalpha(substr(Study_day_IP,length(Study_day_IP)-1, 2)) = 0 then compress(substr(Study_day_IP,length(Study_day_IP)-1, 2))
when anyalpha(substr(Study_day_IP,length(Study_day_IP), 1)) = 0 then compress(substr(Study_day_IP,length(Study_day_IP), 1))
else '1'
end as Study_day_IP_Num
from have a
;
quit;
/* extracting the string portion out of study_day_ip */
data tmp_want2;
format Study_day_IP_Str $32.; length Study_day_IP_Str $32.;
set tmp_want1;
if
Study_day_IP eq '' then Study_day_IP_Str = '';
else
Study_day_IP_Str = substr(Study_day_IP, 1, length(Study_day_IP) - length(Study_day_IP_Num));
run;
/* COl X */
proc sort data = tmp_want2;
by Subject_ID Date;
run;
data tmp_want3;
set tmp_want2;
retain Study_day_IP_Num1;
retain Study_day_IP_Str1;
if Study_day_IP_num ne '' then do;
Study_day_IP_Num1 = input(Study_day_IP_Num,best.);
Study_day_IP_Str1 = Study_day_IP_Str;
end;
else do;
Study_day_IP_Num1 = Study_day_IP_Num1 + 1;
Study_day_IP_Str1 = Study_day_IP_Str1;
end;
by Subject_ID Date;
Col_X = trim(Study_day_IP_Str1) || compress(Study_day_IP_Num1) ;
run;
/* COl Y - COmputing Lag and Lead */
proc sort data = tmp_want3;
by Subject_ID Date;
run;
data tmp_want4;
set tmp_want3;
by Subject_ID Date;
Lag_Study_day_IP_Str1 = lag(Study_day_IP_Num1);
run;
proc sort data = tmp_want4;
by Subject_ID descending Date;
run;
data tmp_want4;
set tmp_want4;
by Subject_ID descending Date;
Lead_Study_day_IP_Str1 = lag(Study_day_IP_Num1);
run;
proc sort data = tmp_want4;
by Subject_ID Date;
run;
/* Final tabl with COl Z */
data want;
set tmp_want4;
if
Study_day_IP_Num1 = Lead_Study_day_IP_Str1 then Col_Z = trim(Study_day_IP_Str1) || compress(Lag_Study_day_IP_Str1) ;
else
Col_Z = Col_X;
drop Study_day_IP_Num1 Lead_Study_day_IP_Str1 Lag_Study_day_IP_Str1 Study_day_IP_Str1 Study_day_IP_Num Study_day_IP_Str;
run;
Thanks.
