comparing one observation with next observation

[reddy19]

Hi All,

I have a question related to comparing one observation with next observation, Where same PT, and same TRT and DOSE_TXT and CMROUTE.

1)CMSTDAT_DTS of one record is prior to the CMENDATC_DT of another record or
2)where CMSTDAT_DTS of record where CMONGO is checked is prior to the  CMENDATC_DT  of another record or
3)where multiple events with CMONGO is checked.

Flag all events of same subject, cmtrt and cmdstxt and cmroute as 'Y'.

Here is the sample data.

pt   trt       route      dose_txt   st_dt         end_dt      cmongo       flag

1    aquaphor (Emollient Ointment) Topical (TOP)    1    2017-01-22  2017-01-28 
1    aquaphor (Emollient Ointment) Topical (TOP)    1    2017-01-28                Ongoing       
2    NaCl 0.9%    Intravenous (IV)   20    2018-07-28          Ongoing    Y    ...satisfying condition 3
2    NaCl 0.9%    Intravenous (IV)   20    2018-08-17          Ongoing       Y    ...satisfying ondition 3
2    Penicillin V Potassium             Oral (PO)    500    2018-07-05    2018-07-08   
2    Penicillin V Potassium             Oral (PO)    500    2018-07-11  2018-07-16     
2    Penicillin V Potassium             Oral (PO)    500    2018-07-20  2018-07-28                 Y        
3    furosemide                         Intravenous (IV)   20      2018-03-30    2018-03-30         
3    furosemide                         Intravenous (IV)   20      2018-03-30               Ongoing         Y   ...satisfying condition 2
3    furosemide                         Intravenous (IV)   20      2018-04-30    2018-04-30                 Y

I'm trying to do this way but not working. can you please suggest me how to proceed.

/* pulling the records having same subject, cmtrt and cmdstxt and cmroute*/
proc sql;
 create table tst as
  select PT,TRT,dos_txt,ROUTE ,st_dt, end_dt ,  CMONGO, count(pt) as cnt from tbl_13
 group by PT,TRT,dos_txt,ROUTE
 order by PT,TRT,dos_txt,ROUTE,st_dt;
quit;

data tst1;
 set tst;
 by PT,TRT,dos_txt,ROUTE;
 where cnt>1;
/*    if CMONGO ne " " and  lag(CMONGO) ne " " and CMONGO=lag(CMONGO) then flag2="Y"; */
/* else if CMONGO eq " " and lag(CMONGO) eq " " and CMSTDATN > lag(CMENDATCN) then flag2="Y";*/
/* else if CMONGO ne " " and lag(CMONGO) eq " " and CMSTDATN < lag(CMENDATCN) then flag2="Y";*/

run;

Thanks in advance.

[Patrick]

@reddy19 

Please post sample data in the form of a tested SAS data step generating such data. Please also post any code using the running man icon so that we're getting something reasonably formatted.

Looking at what you've posted I don't feel motivated to answer as I would first need to invest time just for cleaning up what you've posted and especially to write a data step to create the sample data.

[reddy19]

Here is the SAS Code, as I was reading from the dataset I didn't posted the code originally. Let me know if you have any other questions.

data tst;

infile datalines missover;

input pt $1 trt $2-35 route $36-54 dose_txt $55-60 st_dt $61-80 end_dt $81-100 cmongo $101-120 ;

datalines;

1 aquaphor (Emollient Ointment) Topical (TOP) 1 2017-01-22 2017-01-28

1 aquaphor (Emollient Ointment) Topical (TOP) 1 2017-01-28 Ongoing

2 NaCl 0.9% Intravenous (IV) 20 2018-07-28 Ongoing

2 NaCl 0.9% Intravenous (IV) 20 2018-08-17 Ongoing

2 Penicillin V Potassium Oral (PO) 500 2018-07-05 2018-07-08

2 Penicillin V Potassium Oral (PO) 500 2018-07-11 2018-07-16

2 Penicillin V Potassium Oral (PO) 500 2018-07-20 2018-07-28

3 furosemide Intravenous (IV) 20 2018-03-30 2018-03-30

3 furosemide Intravenous (IV) 20 2018-03-30 Ongoing

3 furosemide Intravenous (IV) 20 2018-04-30 2018-04-30

;

run;

/* pulling the records having same subject, cmtrt and cmdstxt and cmroute*/

proc sql;

create table tst1 as

select PT,TRT,dos_txt,ROUTE ,st_dt, end_dt , CMONGO, count(pt) as cnt from tst

group by PT,TRT,dos_txt,ROUTE

order by PT,TRT,dos_txt,ROUTE,st_dt;

quit;

data tst2;

set tst1;

by PT,TRT,dos_txt,ROUTE;

where cnt>1;

if CMONGO ne " " and lag(CMONGO) ne " " and CMONGO=lag(CMONGO) then

flag2="Y";

else if CMONGO eq " " and lag(CMONGO) eq " " and CMSTDATN > lag(CMENDATCN) then

flag2="Y";

else if CMONGO ne " " and lag(CMONGO) eq " " and CMSTDATN < lag(CMENDATCN) then

flag2="Y";

run;

Thank you.

[ChrisNZ]

1.The code you posted does not run. Use the {i} or running man icin.

And test that what is posted runs.

2. I dont think you need the SQL step.

Something like this should work:

data WANT;
  set HAVE;
  by PT TRT DOS_TXT ROUTE;
  if ^(first.ROUTE & last.ROUTE);
..   do your tests ...
run;

[ballardw]

Data step that will execute:

data tst;
infile datalines missover; 
input pt $1 trt $2-35 route $36-54 dose_txt $55-60 st_dt $61-80 end_dt $81-100 cmongo $101-120 ;
datalines;
1 aquaphor (Emollient Ointment)    Topical (TOP)      1     2017-01-22          2017-01-28   
1 aquaphor (Emollient Ointment)    Topical (TOP)      1     2017-01-28                              Ongoing
2 NaCl 0.9%                        Intravenous (IV)   20    2018-07-28                              Ongoing
2 NaCl 0.9%                        Intravenous (IV)   20    2018-08-17                              Ongoing
2 Penicillin V Potassium           Oral (PO)          500   2018-07-05          2018-07-08
2 Penicillin V Potassium           Oral (PO)          500   2018-07-11          2018-07-16
2 Penicillin V Potassium           Oral (PO)          500   2018-07-20          2018-07-28
3 furosemide                       Intravenous (IV)   20    2018-03-30          2018-03-30
3 furosemide                       Intravenous (IV)   20    2018-03-30                              Ongoing
3 furosemide                       Intravenous (IV)   20    2018-04-30          2018-04-30
;
run;
 

I seriously question having your date variables as character though.

[ChrisNZ]

So many things wrong in this code, like:

1. The variable DOSE_TXT changes name to DOSE_TXT

2. The BY statement does not use commas

3. You use variable CMENDATCN from table TST1 when you never saved it there.

Please do you home work and provide a clear question.

You should show the inputs (@ballardw did it for you here)  and the wanted outputs.