topic Split records based on group date ranges in SAS Programming

Split records based on group date ranges

raja777pharma — Thu, 24 Aug 2023 15:21:29 GMT

Hi,

I need to split the record based on a group of date ranges.

In picture 2 ATRT='TENOFOVIR ALAFENAMIDE (TAF)' record ASTDT and AENDT dates fall into two phases from picture 1 ,

so i need to make new records as in picture 3.

Picture 1

Picture 2

Picture 3

please let me know ho to do it.

Thank you,

Raja.

Re: Split records based on group date ranges

PaigeMiller — Thu, 24 Aug 2023 12:01:41 GMT

Please provide data as working SAS data step code, as you did in a previous thread.

Re: Split records based on group date ranges

raja777pharma — Thu, 24 Aug 2023 12:50:46 GMT

Hi,

Below is the CSV data;

Picture1 data:

USUBJID,APHASE1,APHASE2,APHASE3,APHASE4,PH1SDT,PH1EDT,PH2SDT,PH2EDT,PH3SDT,PH3EDT,PH4SDT,PH4EDT
73763989HPB2003-100004,Screening,Open-label,Follow-up,,25MAR2021,19APR2021,20APR2021,21MAR2022,22MAR2022,16FEB2023,,

Picture2 data:

USUBJID,EXLNKID,AOCCUR,ATRT,ADOSE,ASTDT,AENDT,AVISITN,APHASE,APHASEN
73763989HPB2003-100004,NA-20211102T1040,Y,TENOFOVIR ALAFENAMIDE (TAF),25,02NOV2021,16FEB2023,28,Open-label,2
73763989HPB2003-100004,NA-20210420T1045,Y,TENOFOVIR DISOPROXIL (TD),245,20APR2021,01NOV2021,0,Open-label,2

Thank you,

Raja

Re: Split records based on group date ranges

PaigeMiller — Thu, 24 Aug 2023 12:51:53 GMT

We need data as SAS data step code, as in the other thread you wrote that I linked to.

Re: Split records based on group date ranges

raja777pharma — Thu, 24 Aug 2023 13:38:22 GMT

Hi ,

Please let me know how i can create sas code with data values by using

CSV file data.

Thank you,

Raja.

Re: Split records based on group date ranges

PaigeMiller — Thu, 24 Aug 2023 13:48:08 GMT

You already have the data in SAS, I know this because you showed us screen captures of data in SAS. You work from the SAS data set, not from a CSV file. Here are examples of what SAS data step code looks like and instructions on how to create such code.

Re: Split records based on group date ranges

raja777pharma — Thu, 24 Aug 2023 14:19:13 GMT

Hi,

Thank you for code.

Here is the data.

data picture2;
  infile datalines dsd truncover;
  input USUBJID:$60. EXLNKID:$30. AOCCUR:$1. ATRT:$200. ADOSE:32. ASTDT:DATE9. AENDT:DATE9. AVISITN:32. APHASE:$40. APHASEN:32.;
  format ASTDT DATE9. AENDT DATE9.;
  label USUBJID="Unique Subject Identifier" EXLNKID="Link ID";
datalines4;
73763989HPB2003-100004,NA-20211102T1040,Y,TENOFOVIR ALAFENAMIDE (TAF),25,02NOV2021,16FEB2023,28,Open-label,2
73763989HPB2003-100004,NA-20210420T1045,Y,TENOFOVIR DISOPROXIL (TD),245,20APR2021,01NOV2021,0,Open-label,2
;;;;

data picture1;
  infile datalines dsd truncover;
  input USUBJID:$60. APHASE1:$9. APHASE2:$10. APHASE3:$9. APHASE4:$6. PH1SDT:DATE9. PH1EDT:DATE9. PH2SDT:DATE9. PH2EDT:DATE9. PH3SDT:DATE9. PH3EDT:DATE9. PH4SDT:DATE9. PH4EDT:DATE9.;
  format PH1SDT DATE9. PH1EDT DATE9. PH2SDT DATE9. PH2EDT DATE9. PH3SDT DATE9. PH3EDT DATE9. PH4SDT DATE9. PH4EDT DATE9.;
  label USUBJID="Unique Subject Identifier" APHASE1="Description of Phase 1" APHASE2="Description of Phase 2" APHASE3="Description of Phase 3" APHASE4="Description of Phase 4" PH1SDT="Phase 1 Start Date" PH1EDT="Phase 1 End Date" PH2SDT="Phase 2 Start Date" PH2EDT="Phase 2 End Date" PH3SDT="Phase 3 Start Date" PH3EDT="Phase 3 End Date" PH4SDT="Phase 4 Start Date" PH4EDT="Phase 4 End Date";
datalines4;
73763989HPB2003-100004,Screening,Open-label,Follow-up,,25MAR2021,19APR2021,20APR2021,21MAR2022,22MAR2022,16FEB2023,,
;;;;

Re: Split records based on group date ranges

ballardw — Thu, 24 Aug 2023 14:22:23 GMT

@raja777pharma wrote:

Hi,

I need to split the record based on a group of date ranges.

In picture 2 ATRT='TENOFOVIR ALAFENAMIDE (TAF)' record ASTDT and AENDT dates fall into two phases from picture 1 ,

so i need to make new records as in picture 3.

You really need to make sure that your question makes some sense in terms of provided "data". You picuter 1 does not show any variable named ASTDT or AENDT. So there is no way we can interpret that request clearly. You actually don't even mention how Picture 1 comes into the process at all.

Provide rules, not pictures. Computers run on rules and without a clearly defined set of rules on how to do something then a proposed solution that works ONLY for the shown data maybe provided but would not work for more of a caste.

Re: Split records based on group date ranges

PaigeMiller — Thu, 24 Aug 2023 14:29:56 GMT

Ok, very good. But now I don't understand the logic, how do you know that the ASTDT = 02NOV2021 and AENDT=16FEB2023 in picture 2 is split in the output into two rows with the dates shown, one row being 02NOV2021 to 21MAR2022 and the next row being 22MAR2022 to 16FEB2023?