topic Re: Formatting dataset by start and end dates in SAS Programming

Formatting data by start and end dates

heretolearn — Fri, 15 Jun 2018 22:45:23 GMT

Hello! I have a dataset of medications that I am trying to format. After having gone through a long list of medications and classifying them by type and dose, below is the dataset that I have managed to create so far.

Id date drug_class ondrug1yes lastfollowupdate

1 1/10/13 A_lowdose 1 4/02/15

1 2/05/13 A_highdose 1 4/02/15

1 3/28/13 Discontinued A 0 4/02/15

2 2/06/08 B_lowdose 1 3/05/10

This is what I would like to have:

Id A_lowdose start end A_highdose start end B_low start end

1 1 1/10/13 2/05/13 1 2/06/13 3/28/13 0 . .

2 0 . . 0 . . 1 2/06/08 3/05/10

Every id is a person on any combination of drugs. I have the date that the person started the med, the date of any dosage change, and the date that a drug was continued (if applicable). If the person did not stop a med, I also have the person's last follow-up date, and I would like to make that their end date of the medication. I created the ondrug1yes numeric variable as an indicator of when a person stopped a drug (0 corresponds to the date when the drug was stopped).

One part that is challenging is when people changed drug dosages. Id 1 started a low dose of A on 1/10/13 and was prescribed a high dose of A on 2/5/13. I want to set the start date of the new dose to be one day after the listed start date (so 2/6/13 instead of 2/5/13).

I imagine that I need to use proc transpose to convert the data from long to broad form. Can anyone give me advice on how to go about doing all this?

Formatting dataset by start and end dates

heretolearn — Fri, 15 Jun 2018 22:46:32 GMT

Id date drug_class ondrug1yes lastfollowupdate

1 1/10/13 A_lowdose 1 4/02/15

1 2/05/13 A_highdose 1 4/02/15

1 3/28/13 Discontinued A 0 4/02/15

2 2/06/08 B_lowdose 1 3/05/10

This is what I would like to have:

Id A_lowdose start end A_highdose start end B_low start end

1 1 1/10/13 2/05/13 1 2/06/13 3/28/13 0 . .

2 0 . . 0 . . 1 2/06/08 3/05/10

I imagine that I need to use proc transpose to convert the data from long to broad form. Can anyone give me advice on how to go about doing all this?

Re: Formatting dataset by start and end dates

KentaMURANAKA — Sat, 16 Jun 2018 05:53:23 GMT

How about

data test;
    input id date$ drug_class$ 15-28 ondrug1yes lastfollowupdate$;
    datalines;
    1 1/10/13 A_lowdose      1 4/02/15
    1 2/05/13 A_highdose     1 4/02/15
    1 3/28/13 Discontinued A 0 4/02/15
    2 2/06/08 B_lowdose      1 3/05/10
    ;
run;
data test_1;
    set test;
    select(drug_class);
        when("A_lowdose") drug_class_num=1;
        when("A_highdose") drug_class_num=2;
        when("Discontinued A") drug_class_num=3;
        when("B_lowdose") drug_class_num=4;
        when("B_highdose") drug_class_num=5;
        when("Discontinued B") drug_class_num=6;
    end;
run;
data dummy;
    attrib id length=8.;
    attrib drug_class_num length=8.;
    attrib drug_class length=$14.;
    do id=1 to 2;
        if id eq 1 then do;
            drug_class_num=1; drug_class="A_lowdose"; output;
            drug_class_num=2; drug_class="A_highdose"; output;
            drug_class_num=3; drug_class="Discontinued A"; output;
            drug_class_num=4; drug_class="B_lowdose"; output;
            drug_class_num=5; drug_class="B_highdose"; output;
            drug_class_num=6; drug_class="Discontinued B"; output;
        end;
        else if id eq 2 then do;
            drug_class_num=1; drug_class="A_lowdose"; output;
            drug_class_num=2; drug_class="A_highdose"; output;
            drug_class_num=3; drug_class="Discontinued A"; output;
            drug_class_num=4; drug_class="B_lowdose"; output;
            drug_class_num=5; drug_class="B_highdose"; output;
            drug_class_num=6; drug_class="Discontinued B"; output;
        end;
    end;
run;
data test_2;
    merge dummy(in=a)
          test_1(in=b)
          ;
    by id drug_class_num drug_class;
    if a;
    date_num=input(date, mmddyy8.);
    lastfollowupdate_num=input(lastfollowupdate, mmddyy8.);
run;
proc transpose data=test_2 prefix=ondrug out=test_3_1(drop=_name_);
    var ondrug1yes;
    by id;
    id drug_class_num;
run;
/*
ONDRUG1: OnDrugFlag where DRUG_CLASS eq "A_lowdose"
ONDRUG2: OnDrugFlag where DRUG_CLASS eq "A_highdose"
ONDRUG2: OnDrugFlag where DRUG_CLASS eq "Discontinued A"
ONDRUG4: OnDrugFlag where DRUG_CLASS eq "B_lowdose"
ONDRUG5: OnDrugFlag where DRUG_CLASS eq "B_highdose"
ONDRUG6: OnDrugFlag where DRUG_CLASS eq "Discontinued B"
*/
proc transpose data=test_2 prefix=date out=test_3_2(drop=_name_);
    var date_num;
    by id;
    id drug_class_num;
run;
/*
DATE1: Date where DRUG_CLASS eq "A_lowdose"
DATE2: Date where DRUG_CLASS eq "A_highdose"
DATE2: Date where DRUG_CLASS eq "Discontinued A"
DATE4: Date where DRUG_CLASS eq "B_lowdose"
DATE5: Date where DRUG_CLASS eq "B_highdose"
DATE6: Date where DRUG_CLASS eq "Discontinued B"
*/
proc transpose data=test_2 prefix=followup out=test_3_3(drop=_name_);
    var lastfollowupdate_num;
    by id;
    id drug_class_num;
run;
/*
FOLLOWUP1: FollowupDate where DRUG_CLASS eq "A_lowdose"
FOLLOWUP2: FollowupDate where DRUG_CLASS eq "A_highdose"
FOLLOWUP2: FollowupDate where DRUG_CLASS eq "Discontinued A"
FOLLOWUP4: FollowupDate where DRUG_CLASS eq "B_lowdose"
FOLLOWUP5: FollowupDate where DRUG_CLASS eq "B_highdose"
FOLLOWUP6: FollowupDate where DRUG_CLASS eq "Discontinued B"
*/
data test_4;
    length id 8. a_lowdose 8.
           start_al end_al $200.
           a_highdose 8.
           start_ah end_ah $200.
           b_lowdose 8.
           start_bl end_bl $200.
           b_highdose 8.
           start_bh end_bh $200.
           ;

    merge test_3_1(in=a)
          test_3_2(in=b)
          test_3_3(in=c)
          ;
    by id;
    if a;
    %macro ondrug(in=, out=);
    select(&in.);
        when(1) &out.=1;
        when(.) &out.=0;
    end;
    %mend ondrug;
    %ondrug(in=ondrug1, out=A_lowdose)
    %ondrug(in=ondrug2, out=A_highdose)
    %ondrug(in=ondrug4, out=B_lowdose)
    %ondrug(in=ondrug5, out=B_highdose)
    %macro date(in1=, out1=, in2=, out2=);
    select;
        when(&in1. ne .) &out1.=put(&in1., mmddyy8.);
        when(&in1. eq .) &out1.="";
    end;
    select;
        when(&in2. ne .) &out2.=put(&in2.+1, mmddyy8.);
        when(&in2. eq .) &out2.="";
    end;
    %mend date;
    %date(in1=date1, out1=start_al, in2=date2, out2=start_ah)
    %date(in1=date4, out1=start_bl, in2=date5, out2=start_bh)
    %macro enddate(if1=, if2=, if3=, out1=, out2=, in1=, in2=, in3=, in4=);
    if &if1. eq 1 and &if2. eq . and &if3. ne 0 then do;
        &out1.=put(&in1., mmddyy8.);
        &out2.="";
    end;
    else if &if1. eq 1 and &if2. eq . and &if3. eq 0 then do;
        &out1.=put(&in2., mmddyy8.);
        &out2.="";
    end;
    else if &if1. eq 1 and &if2. eq 1 and &if3. ne 0 then do;
        &out1.=put(&in3., mmddyy8.);
        &out2.=put(&in4., mmddyy8.);
    end;
    else if &if1. eq 1 and &if2. eq 1 and &if3. eq 0 then do;
        &out1.=put(&in3., mmddyy8.);
        &out2.=put(&in2., mmddyy8.);
    end;
    %mend enddate;
    %enddate(if1=ondrug1, if2=ondrug2, if3=ondrug3, out1=end_al, out2=end_ah ,in1=followup1, in2=date3, in3=date2, in4=followup2)
    %enddate(if1=ondrug4, if2=ondrug5, if3=ondrug6, out1=end_bl, out2=end_bh ,in1=followup4, in2=date6, in3=date5, in4=followup5)
run;

Re: Formatting dataset by start and end dates

heretolearn — Sat, 16 Jun 2018 06:20:53 GMT

KentaMURANAKA, you are AWESOME! Thank you so much!

Re: Formatting dataset by start and end dates

heretolearn — Sat, 16 Jun 2018 06:23:33 GMT

One question! For the following part of code, you included "do id=1 to 2;" and then followed that by a separate section of code for each id.

I have >200 ids in this dataset. Is there a faster way to do this instead of entering code for each id?

Re: Formatting dataset by start and end dates

KentaMURANAKA — Sat, 16 Jun 2018 06:55:32 GMT

Hi, heretolearn:

If you have 4 Subjects' data, you can accomplish your objective by submitting the following code & making dummy, I think.

data test;
    input id date$ drug_class$ 15-28 ondrug1yes lastfollowupdate$;
    datalines;
    1 xxxxxxx A_lowdose      1 xxxxxxx
    1 xxxxxxx A_highdose     1 xxxxxxx
    1 xxxxxxx Discontinued A 0 xxxxxxx
    2 xxxxxxx B_lowdose      1 xxxxxxx
    3 xxxxxxx A_lowdose      1 xxxxxxx
    3 xxxxxxx A_highdose     1 xxxxxxx
    3 xxxxxxx Discontinued A 0 xxxxxxx
    4 xxxxxxx B_lowdose      1 xxxxxxx
    ;
run;
data test_1;
    set test;
    if index(drug_class, "A") gt 0 then trta=1;
    else if index(drug_class, "B") gt 0 then trta=2;
run;
proc sort data=test_1 nodupkey out=test_2(keep=id trta);
    by id;
run;
data dummy;
    attrib id length=8.;
    attrib drug_class_num length=8.;
    attrib drug_class length=$14.;
    set test_2;
    drug_class_num=1; drug_class="A_lowdose"; output;
    drug_class_num=2; drug_class="A_highdose"; output;
    drug_class_num=3; drug_class="Discontinued A"; output;
    drug_class_num=4; drug_class="B_lowdose"; output;
    drug_class_num=5; drug_class="B_highdose"; output;
    drug_class_num=6; drug_class="Discontinued B"; output;
run;

Re: Formatting dataset by start and end dates

KentaMURANAKA — Sat, 16 Jun 2018 07:07:55 GMT

Hi, heretolearn:

In my first code, you don't need following part in order to make dummy data.

I'm sorry!

do id=1 to 2;
if id eq 1 then do;
XXXXXXXXXXX
XXXXXXXXXXX
end;
else if id eq 2 then do;
drug_class_num=1; drug_class="A_lowdose"; output;
drug_class_num=2; drug_class="A_highdose"; output;
drug_class_num=3; drug_class="Discontinued A"; output;
drug_class_num=4; drug_class="B_lowdose"; output;
drug_class_num=5; drug_class="B_highdose"; output;
drug_class_num=6; drug_class="Discontinued B"; output;
end;
end;

Re: Formatting dataset by start and end dates

KentaMURANAKA — Sun, 17 Jun 2018 04:16:45 GMT

Hi, heretolearn:

Summary of my answers.

If you have >200 Subjects' data & your data satisfy the following conditions, you will get your objective by submitting the code below.

1. Variable DRUG_CLASS includes only them (A_lowdose, A_highdose, Discontinued A, B_lowdose, B_highdose, discontinued B)

2. All Variables in your data are equal to my data TEST's.

Your objective may be in TEST_4 (variables from ID to END_BH). Good luck.

data test;
    input id date$ drug_class$ 15-28 ondrug1yes lastfollowupdate$;
    datalines;
    1 1/10/13 A_lowdose      1 4/02/15
    1 2/05/13 A_highdose     1 4/02/15
    1 3/28/13 Discontinued A 0 4/02/15
    2 2/06/08 B_lowdose      1 3/05/10
    ;
run;
data test_1;
    set test;
    select(drug_class);
        when("A_lowdose") drug_class_num=1;
        when("A_highdose") drug_class_num=2;
        when("Discontinued A") drug_class_num=3;
        when("B_lowdose") drug_class_num=4;
        when("B_highdose") drug_class_num=5;
        when("Discontinued B") drug_class_num=6;
    end;
    if index(drug_class, "A") gt 0 then trta=1;
    else if index(drug_class, "B") gt 0 then trta=2;
run;
proc sort data=test_1 nodupkey out=test_2(keep=id trta);
    by id;
run;
data dummy;
    attrib id length=8.;
    attrib drug_class_num length=8.;
    attrib drug_class length=$14.;
    set test_2;
    drug_class_num=1; drug_class="A_lowdose"; output;
    drug_class_num=2; drug_class="A_highdose"; output;
    drug_class_num=3; drug_class="Discontinued A"; output;
    drug_class_num=4; drug_class="B_lowdose"; output;
    drug_class_num=5; drug_class="B_highdose"; output;
    drug_class_num=6; drug_class="Discontinued B"; output;
run;
data test_2;
    merge dummy(in=a)
          test_1(in=b)
          ;
    by id drug_class_num drug_class;
    if a;
    date_num=input(date, mmddyy8.);
    lastfollowupdate_num=input(lastfollowupdate, mmddyy8.);
run;
proc transpose data=test_2 prefix=ondrug out=test_3_1(drop=_name_);
    var ondrug1yes;
    by id;
    id drug_class_num;
run;
/*
ONDRUG1: OnDrugFlag where DRUG_CLASS eq "A_lowdose"
ONDRUG2: OnDrugFlag where DRUG_CLASS eq "A_highdose"
ONDRUG2: OnDrugFlag where DRUG_CLASS eq "Discontinued A"
ONDRUG4: OnDrugFlag where DRUG_CLASS eq "B_lowdose"
ONDRUG5: OnDrugFlag where DRUG_CLASS eq "B_highdose"
ONDRUG6: OnDrugFlag where DRUG_CLASS eq "Discontinued B"
*/
proc transpose data=test_2 prefix=date out=test_3_2(drop=_name_);
    var date_num;
    by id;
    id drug_class_num;
run;
/*
DATE1: Date where DRUG_CLASS eq "A_lowdose"
DATE2: Date where DRUG_CLASS eq "A_highdose"
DATE2: Date where DRUG_CLASS eq "Discontinued A"
DATE4: Date where DRUG_CLASS eq "B_lowdose"
DATE5: Date where DRUG_CLASS eq "B_highdose"
DATE6: Date where DRUG_CLASS eq "Discontinued B"
*/
proc transpose data=test_2 prefix=followup out=test_3_3(drop=_name_);
    var lastfollowupdate_num;
    by id;
    id drug_class_num;
run;
/*
FOLLOWUP1: FollowupDate where DRUG_CLASS eq "A_lowdose"
FOLLOWUP2: FollowupDate where DRUG_CLASS eq "A_highdose"
FOLLOWUP2: FollowupDate where DRUG_CLASS eq "Discontinued A"
FOLLOWUP4: FollowupDate where DRUG_CLASS eq "B_lowdose"
FOLLOWUP5: FollowupDate where DRUG_CLASS eq "B_highdose"
FOLLOWUP6: FollowupDate where DRUG_CLASS eq "Discontinued B"
*/
data test_4;
    length id 8. a_lowdose 8.
           start_al end_al $200.
           a_highdose 8.
           start_ah end_ah $200.
           b_lowdose 8.
           start_bl end_bl $200.
           b_highdose 8.
           start_bh end_bh $200.
           ;

    merge test_3_1(in=a)
          test_3_2(in=b)
          test_3_3(in=c)
          ;
    by id;
    if a;
    %macro ondrug(in=, out=);
    select(&in.);
        when(1) &out.=1;
        when(.) &out.=0;
    end;
    %mend ondrug;
    %ondrug(in=ondrug1, out=A_lowdose)
    %ondrug(in=ondrug2, out=A_highdose)
    %ondrug(in=ondrug4, out=B_lowdose)
    %ondrug(in=ondrug5, out=B_highdose)
    %macro date(in1=, out1=, in2=, out2=);
    select;
        when(&in1. ne .) &out1.=put(&in1., mmddyy8.);
        when(&in1. eq .) &out1.="";
    end;
    select;
        when(&in2. ne .) &out2.=put(&in2.+1, mmddyy8.);
        when(&in2. eq .) &out2.="";
    end;
    %mend date;
    %date(in1=date1, out1=start_al, in2=date2, out2=start_ah)
    %date(in1=date4, out1=start_bl, in2=date5, out2=start_bh)
    %macro enddate(if1=, if2=, if3=, out1=, out2=, in1=, in2=, in3=, in4=);
    if &if1. eq 1 and &if2. eq . and &if3. ne 0 then do;
        &out1.=put(&in1., mmddyy8.);
        &out2.="";
    end;
    else if &if1. eq 1 and &if2. eq . and &if3. eq 0 then do;
        &out1.=put(&in2., mmddyy8.);
        &out2.="";
    end;
    else if &if1. eq 1 and &if2. eq 1 and &if3. ne 0 then do;
        &out1.=put(&in3., mmddyy8.);
        &out2.=put(&in4., mmddyy8.);
    end;
    else if &if1. eq 1 and &if2. eq 1 and &if3. eq 0 then do;
        &out1.=put(&in3., mmddyy8.);
        &out2.=put(&in2., mmddyy8.);
    end;
    %mend enddate;
    %enddate(if1=ondrug1, if2=ondrug2, if3=ondrug3, out1=end_al, out2=end_ah ,in1=followup1, in2=date3, in3=date2, in4=followup2)
    %enddate(if1=ondrug4, if2=ondrug5, if3=ondrug6, out1=end_bl, out2=end_bh ,in1=followup4, in2=date6, in3=date5, in4=followup5)
run;

Re: Formatting dataset by start and end dates

heretolearn — Sun, 17 Jun 2018 16:27:00 GMT

Hi KentaMURANAKA,

Thank you so much for following up! I really appreciate this!

I have one other question/request. After using your code and working around with my dataset, I have realized that for the sake of the analysis that I need to run, it would be easier if I could do the following:

As you know, this is my start dataset:

Id date drug_class ondrug1yes lastfollowupdate

1 1/10/13 A_lowdose 1 4/02/15

1 2/05/13 A_highdose 1 4/02/15

1 3/28/13 Discontinued A 0 4/02/15

2 2/06/08 B_lowdose 1 3/05/10

It will be easier for me to run the analyses that I need to do if I can transform the above dataset to this:

id start_date end_date drug_class

1 1/10/13 2/05/13 A_lowdose

1 2/06/13 3/28/13 A_highdose

2 2/06/08 3/05/10 B_lowdose

I'm hoping that the code for this might be easier? Like last time, I'm trying to get the start_date of A_highdose to actually be one day after the listed date. And for those with no discontinuation date, I just want the last follow up date to be the end_date.

Would you happen to know how to do this?

Thank you again! Your help has been incredible!

Re: Formatting dataset by start and end dates

KentaMURANAKA — Sun, 17 Jun 2018 23:43:48 GMT

Hi, heretolearn:

This is continuation from my previous code.

proc transpose data=test_4 out=test_5_1;
    var start_al start_ah start_bl start_bh;
    by id;
run;
proc transpose data=test_4 out=test_5_2(rename=(col1=col2));
    var end_al end_ah end_bl end_bh;
    by id;
run;
data test_5_1_1;
    set test_5_1;
    select(_name_);
        when("start_al") sort=1;
        when("start_ah") sort=2;
        when("start_bl") sort=3;
        when("start_bh") sort=4;
    end;
    drop _name_;
run;
proc sort data=test_5_1_1 out=test_5_1_2;
    by id sort;
run;
data test_5_2_1;
    set test_5_2;
    select(_name_);
        when("end_al") sort=1;
        when("end_ah") sort=2;
        when("end_bl") sort=3;
        when("end_bh") sort=4;
    end;
    drop _name_;
run;
proc sort data=test_5_2_1 out=test_5_2_2;
    by id sort;
run;
data test_6;
    attrib id
        length=8.
           col1 col2 drug_class
        length=$200.
        ;
    merge test_5_1_2(in=a)
          test_5_2_2(in=b)
          ;
    by id sort;
    if a;
    select(sort);
        when(1) drug_class="A_lowdose";
        when(2) drug_class="A_highdose";
        when(3) drug_class="B_lowdose";
        when(4) drug_class="B_highdose";
    end;
    drop sort;
    if col1 ne "" and col2 ne "";
    rename col1=start_date col2=end_date;
run;

or you want to start from dataset TEST?

If so, I'll make it.

And my code is not clear one, so if it's difficult to understand, please give me feedback.

Re: Formatting dataset by start and end dates

heretolearn — Tue, 19 Jun 2018 04:55:52 GMT

KentaMURANAKA, thank you again! You have been incredibly helpful! 🙂