Data manipulation

Accepted Solution Solved
Reply
Contributor
Posts: 32
Accepted Solution

Data manipulation

[ Edited ]

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.

 


Accepted Solutions
Solution
‎01-20-2017 10:49 AM
Super User
Posts: 11,107

Re: Data manipulation

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;

View solution in original post


All Replies
Solution
‎01-20-2017 10:49 AM
Super User
Posts: 11,107

Re: Data manipulation

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;
Contributor
Posts: 32

Re: Data manipulation

Yes, i am sorry i forgot to mention this.

I have unique identifier for every patient in each row

Contributor
Posts: 32

Re: Data manipulation

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

 

 

Contributor
Posts: 32

Re: Data manipulation

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?

Super User
Super User
Posts: 7,695

Re: Data manipulation

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.

 

Contributor
Posts: 32

Re: Data manipulation

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

Contributor
Posts: 32

Re: Data manipulation

Thank you very much RW9. you guys are awesome.

Super Contributor
Posts: 474

Re: Data manipulation

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

 

 

Contributor
Posts: 32

Re: Data manipulation

Thanks Daniel for your reply

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 201 views
  • 0 likes
  • 4 in conversation