topic Re: Data manipulation in SAS Programming

Data manipulation

AMFR — Fri, 20 Jan 2017 15:19:05 GMT

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.

Re: Data manipulation

ballardw — Fri, 20 Jan 2017 15:10:42 GMT

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;

Re: Data manipulation

RW9 — Fri, 20 Jan 2017 15:11:24 GMT

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.

Re: Data manipulation

AMFR — Fri, 20 Jan 2017 15:20:21 GMT

Yes, i am sorry i forgot to mention this.

I have unique identifier for every patient in each row

Re: Data manipulation

AMFR — Fri, 20 Jan 2017 15:23:09 GMT

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

Re: Data manipulation

AMFR — Fri, 20 Jan 2017 15:50:49 GMT

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

Re: Data manipulation

AMFR — Fri, 20 Jan 2017 16:11:42 GMT

Thank you very much RW9. you guys are awesome.

Re: Data manipulation

DanielSantos — Fri, 20 Jan 2017 16:49:47 GMT

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

Re: Data manipulation

AMFR — Wed, 25 Jan 2017 17:27:14 GMT

Thanks Daniel for your reply

Re: Data manipulation

AMFR — Wed, 25 Jan 2017 17:29:39 GMT

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?