What is the actual goal here? Is it to make this counter variable? Or is that just some intermediate step towards your true objective?
I can recreate your variable.
data want;
set test(rename=(AJOINID=EXPECT));
by USUBJID AELLT notsorted;
if first.usubjid then AJOINID=0;
if first.AELLT or (astdt > lag(aendt) > .Z ) then AJOINID+1;
run;
proc compare;
var expect;
with ajoinid;
run;But somehow I doubt that is actually what you want to do. It will have trouble with real data. If you have two or more AELLT term values that have overlapping time periods how can you put them into an order that would make that code notice the overlapping time periods for one or more of the individual AELLT terms?
And also why did you NOT consider these two records as overlapping?
Ok, I got this one but at one point I am stuck
I am using below code to get AJOINID values.
data test;
SET test;
by usubjid astdt aellt;
if first.aellt=1 then ajoinid+1;
if first.usubjid=1 then ajoinid=1;
run;
but as you see, at highlighted part, I am getting wrong values.
I only want to repeat number when for same USUBJID , same AELLT if previous AENDT same as current ASTDTC
on the observation no. 19 then previous AENDT is missing and current ASTDTC is 27MAR2025, so I want to continue with AJOINID = 10 and 11 for last observation.
Please help me with this
I am attaching the code for input dataset .
data test;
infile datalines dsd dlm=',' truncover;
length USUBJID $20 AELLT $50 ASTDT $9 AENDT $9 ASTDTC $9;
input USUBJID $ AELLT $ ASTDT $ AENDT $ AJOINID ASTDTC $;
datalines;
101,Fat,17APR2025,27APR2025,1,17APR2025
101,Fat,17APR2025,.,1,27APR2025
101,Nausea,17APR2025,29APR2025,2,17APR2025
101,Anorexia,23APR2025,29APR2025,3,23APR2025
101,Arthralgia,27APR2025,14MAY2025,4,27APR2025
101,Arthralgia,27APR2025,15MAY2025,4,14MAY2025
101,Arthralgia,27APR2025,.,4,15MAY2025
101,Hemorrhoids,05MAY2025,.,5,05MAY2025
101,Vomiting,27MAY2025,27MAY2025,6,27MAY2025
201,Genital bleeding,18JAN2025,30JAN2025,1,18JAN2025
201,Nausea,23JAN2025,.,2,23JAN2025
201,Pelvic pain,23JAN2025,.,3,23JAN2025
201,Genital bleeding,08FEB2025,13FEB2025,4,08FEB2025
201,Asthenia,27FEB2025,.,5,27FEB2025
201,Vaginal dryness,27FEB2025,.,6,27FEB2025
201,Genital bleeding,01MAR2025,01MAR2025,7,.
201,Diarrhea,27MAR2025,.,8,27MAR2025
201,Rash,27MAR2025,.,9,27MAR2025
201,Rash,27MAR2025,.,9,27MAR2025
201,Hyponatremia,08MAY2025,.,10,08MAY2025
;
run;
data test (drop = astdtx aendtx astdtcx ajoinid);
retain usubjid aellt astdtc aendt astdt ajoinid ;
set test (rename=(astdt=astdtx aendt=aendtx astdtc =astdtcx ));
astdt = input(strip(astdtx),??date9.);
aendt = input(strip(aendtx),??date9.);
astdtc = input(strip(astdtcx),??date9.);
format astdt aendt astdtc date9.;
run;
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.
-
1478 views
-
1 like
-
-
