Data manipulation

[AMFR]

Hello everyone,

Below is made up data, very similiar to data i am working on. This data is created by merging two files, one file with surgery time in and time out and second file has temperature data. And for my analysis i need temperature data for the date of surgery and afterwards. I donot need the pre-op data. Can some one please help me to do it. Only patient is character and else are numeric variables. Id variable is common variable in both files

Patient         id             surgery_in                  surgery_out                   date_temp                       temperature

                   123

                   123                 .                                      .                              4/5/2014                           37.0

                   123                .                                       .                              4/6/2014                           37.5

                   123                 .                                            .                        4/7/2014                           37.2

Mary           123        8APR14:07:12:00            8APR14:13:15:00          4/8/2014                           37.5

                   123                .                                             .                        4/9/2014                           37.2

                   123                .                                              .                      4/10/2014                         37.0

                   123                .                                              .                      4/11/2014                         37.1

Thanks.

[ballardw · Accepted solution]

Is the patient information ONLY on the record with the surgery date? If so how do we know when the data stops for that patient?

If you have patient data on every record this should work:

proc sort data=have;
   by patient datetemp;
run;

data want;
   set have;
   retain start;
   if first.patient then start=0;
   If not missing(surgery_in) then start=1;

   If start=1;
   drop start;
run;

[AMFR]

Yes, i am sorry i forgot to mention this.

I have unique identifier for every patient in each row

[AMFR]

Thanks ballardw, your codes are doing the trick. I am very grateful.

[AMFR]

Hi ballardw,

If i am looking for the pre-op plus the day of surgery data (ignoring the post-op data), how can i do that?

[RW9]

Post data in the form of a datastep, it helps describe structure and avoids us typing that in.  

Question 1, when you merge in the data, why is patient surgeryin/out on every record?  I mean how are we to know when Patient Mary's data ends and the next patient starts?  This is bad data modelling.  If you have the data on every row then something like this should work:

data want;
  set have;
  by patient;
  retain tosave;
  if first.patient then tosave=0;
  if surgery_in=date_temp then tosave=1;
  if tosave then output;
run;

This just sets a flag when it hits the point at which you want to output the data and then outputs from there onwards within each patient by group.  You could also reverse sort the data, so descending date, and output from start of each patient until dates match.

[AMFR]

We do have a unique identifier for every patient. I have updated my post.

[AMFR]

Thank you very much RW9. you guys are awesome.

[DanielSantos]

Hi.

And for the sake of diversity, the SQL way (not tested but assuming SURGERY_IN is datetime value and DATE_TEMP is date value) :

proc sql noprint;
create table want as
select a.* from have as a,
(select distinct PATIENT, datepart(SURGERY_IN) as DATE_SURGERY from have) as b
where a.PATIENT eq b.PATIENT and a.DATE_TEMP ge b.DATE_SURGERY
order by a.DATE_TEMP;
quit;

Daniel Santos @ www.cgd.pt

[AMFR]

Thanks Daniel for your reply