topic comparing one observation with next observation in SAS Programming

comparing one observation with next observation

reddy19 — Thu, 11 Apr 2019 23:06:34 GMT

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.

Re: comparing one observation with next observation

Patrick — Sun, 14 Apr 2019 22:24:03 GMT

@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.

Re: comparing one observation with next observation

reddy19 — Fri, 12 Apr 2019 01:00:35 GMT

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.

Re: comparing one observation with next observation

ChrisNZ — Fri, 12 Apr 2019 03:53:40 GMT

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;

Re: comparing one observation with next observation

ballardw — Fri, 12 Apr 2019 16:23:07 GMT

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.

Re: comparing one observation with next observation

ChrisNZ — Sun, 14 Apr 2019 22:09:35 GMT

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.