Again I am not sure why it matters what numbers are used.  I would also push back on that requirement to see if there is any basis for it.   You can always renumber. proc sort data=test; by usubjid aellt astdt fake_end ; run; data step1 ; set test ; by usubjid aellt ; if first.usubjid then GROUP=0; if first.aellt or (astdt > lag(aendt)) then GROUP+1; run; proc sql ; create table renumber as select usubjid,GROUP,aellt,min(astdt) as first_date format=date9. from step1 group by usubjid,GROUP,aellt order by usubjid,first_date,aellt ; quit; data renumber; set renumber; by usubjid first_date aellt; if first.usubjid then AJOINID=0; AJOINID+1; keep usubjid GROUP AJOINID; run; proc sort data=renumber; by usubjid group; run; data want; merge step1 renumber; by usubjid group; run; proc sort data=want; by usubjid astdt aellt fake_end ; run; proc print; run; ... View more

In a DATA step, use by usubjid; At first.usubjid, set join to 1. If not first.usubjid, and either ae is not equal to lag(ae) or aestdtc is greater than lag(aendt), increment join by 1. ... View more

What do you mean by LIBRARY? In SAS you can use the LIBNAME statement to make a library to points to where SAS datasets and other SAS objects are stored.   But an XPT file is NOT a SAS object that would be stored in such a library.   If you mean a directory of files (what some call a "folder") then just use normal tools for listing the files in a directory.  This question has been asked many times.  Here is one: https://communities.sas.com/t5/SAS-Programming/SAS-Macro-to-Check-Each-Subfolder-Within-a-Directory-and-Import/td-p/868953   Note that you will also need to take care once you have the list of XPT files as that extension has no fixed meaning.  It could be a SAS V5 Xport file produced by the XPORT libref engine.  It could be a transport file produced by PROC CPORT.  It could be a SAS V7/8/9 Xport file produced by the %LOC2XPT() system macro.  Or could be something that is not at all related to SAS.   ... View more

Hi @Kurt_Bremser , Thanks, it worked 🙂 ... View more

Hi @Kurt_Bremser  do you have solution for below scenario... My supervisor has another bizarre request with RANK value in this data      This is how we develop RANK value up till now. but my client need highlighted value for obs. 7 and 8 as 5 and not 1   like this...   Reason: He says...while deriving  RANK value for obs. 7, he wanted to check latest of LLT = urea value, which is obs. no. 4 and AENDT of obs. no. 4 is not equal to ASTDT of obs. no. 7, so we need to keep adding RANK +1. currently while deriving  RANK value for obs. 7, we are considering obs. no. 3.   can we modify our existing code for this requirement. sorry to trouble you with this again. I am also attaching the datalines for this. data test; input @1 usubjid $3. @5 ae $8. @14 llt $8. @23 astdt date9. @33 aendt date9. ; format astdt aendt date9.; datalines; 410 amino alan 19JUL2022 08AUG2022 410 amino aspar 01AUG2022 08AUG2022 410 Toxicity urea 26JUL2022 18AUG2022 410 Toxicity urea 02AUG2022 15AUG2022 410 Toxicity prot 08AUG2022 21SEP2022 410 Toxicity creat 11AUG2022 22AUG2022 410 Toxicity urea 18AUG2022 18AUG2022 410 Toxicity urea 18AUG2022 02SEP2022 410 Toxicity minuria 29AUG2022 21SEP2022 410 Toxicity urea dec 15SEP2022 21SEP2022 720 Toxicity estim 27DEC2022 17JAN2023 720 Toxicity urea 03JAN2023 17JAN2023 720 Toxicity creat 10JAN2023 24JAN2023 720 Toxicity estim 17JAN2023 28FEB2023 720 Toxicity urea 31JAN2023 14FEB2023 720 Toxicity creat 31JAN2023 14FEB2023 720 Toxicity urea 21FEB2023 28FEB2023 720 Toxicity estim 28FEB2023 21MAR2023 ; run; ... View more

Hi Kurt,  Yes, sure... I was also looking for more on DECLARE HASH. Surely will get back to you if more information needed. Really appreciate your help. However, I need your help with one more variable with this data. I will start different discussion for this. Hope to see you there 🙂     ... View more

  Hi Kurt,   Thanks for the solution. it worked. However, I have one more scenario.   with your derivation, we got JOIN value 2 for 7th observation. we need the JOIN value as 1 for this observation. as we don't know the end date of pervious observation (or missing end date for any of the previous records), so we consider this as cutaneous overlap AE.   The last observation JOIN value 2 is correct, though it has missing end date, but no other previous record has missing end dates.   do you have any solution for this. I am pasting datalines for this test dataset below for refrence.     data test1; informat astdt date9. aendt date9.; input @1 usubjid $3. @5 astdt date9. @15 aendt date9. @25 ae $15.; format astdt aendt date9.; datalines; 101 10JUN2016 06JUL2016 vomit 101 06JUL2016 08JUL2016 vomit 101 06JUL2016 08JUL2016 vomit 101 08JUL2016 22JUL2016 vomit 101 22JUL2016 28JUL2016 vomit 101 28JUL2016 . vomit 101 16SEP2016 . vomit 202 27DEC2022 17JAN2023 vomit 202 03JAN2023 17JAN2023 vomit 202 10JAN2023 24JAN2023 vomit 202 17JAN2023 28FEB2023 vomit 202 31JAN2023 14FEB2023 vomit 202 31JAN2023 14FEB2023 vomit 202 21FEB2023 28FEB2023 vomit 202 28FEB2023 21MAR2023 vomit 202 11APR2023 . vomit ; run;   ... View more

For this example, you don't actually need any macro quoting, you could just use: 8 %chk (result = Intensity (%) ) val=Intensity (%) NOTE: The data set WORK.ONE has 1 observations and 1 variables. ... View more

No. You can do something similar using SQL Naitive Window Functions, but sadly, they're not supported in Proc SQL. ... View more

Sure, but you might try to aim it at this board: SAS Health and Life Science ... View more

Hi: As @Reeza has explained, it would be much easier to combine the RTF files using Word or an Adobe product. The reason is that an RTF file, by Microsoft specification, has opening and closing control strings the denote the beginning and end of an RTF file. To combine the files using SAS, you'd have to write a program to strip out these open/close control strings, carefully -- strip the close out of the first file, strip the open and close out of the 2 middle files and then strip the open but leave the close on the last file. Doing this in Word or Adobe would be easier than writing or debugging the SAS program to do it. And unfortunately, if you're not careful, it is possible to corrupt an RTF file so it is not "openable" if you take out the wrong control strings. Just some background on why you'd use a different tool. Cynthia ... View more

is it possible to show how to achieve this programmatically ? ... View more