Spacing out measures based on a certain criteria

[mitrakos]

Hello! This is a bit of a complicated question so please forgive me if I explain it poorly. 

I have multiple datasets of blood pressure files that have a range of dates of where blood pressure readings are. Here is an example

ID	Date	Systolic	Diastolic	Pulse	Notes
1	Start Nov 8 End Nov 10 - 6	109	78	73
1	Start Nov 8 End Nov 10 - 5	110	84	79
1	Start Nov 8 End Nov 10 - 4	107	78	67
1	Start Nov 8 End Nov 10 - 3	114	83	63
1	Start Nov 8 End Nov 10 - 2	115	81	58
1	Start Nov 8 End Nov 10 - 1	110	77	63

There are 6 measures spread out over 3 days. We do not know when exactly these blood pressure readings were taken, so we decide for this to take the 6 measures equally spread over the 3 days, i.e. 2 measures a day. The end table of above would look like this:

ID	Date	Systolic	Diastolic	Pulse	Note
1	Nov 10	109	78	73
1	Nov 10	110	84	79
1	Nov 9	107	78	67
1	Nov 9	114	83	63
1	Nov 8	115	81	58
1	Nov 8	110	77	63

This is a very simplified version and the data can get more complicated. Some have # of days and # of measures that are not divisible by each other. I'm wondering if there is some sort of SAS code I could write that would automate the process of taking the measures and spreading them out equally (or as equal as possible) over the desired date range. 

Thank you so much for reading this, I hope I explained this as best as I could!

[PaigeMiller]

So as I see the description, you don't want the blood pressure and pulse numbers won't change, it is just the date that you want to change.

But, when you provide only a very small example of the data, we can't generalize to more difficult situation and then write code that handles many or all situations. You need to actually show us some examples where the problem isn't as simple as the one you have shown, like a bunch of cases where, as you say: "Some have # of days and # of measures that are not divisible by each other."

Lastly, no one wants to work with data as screen captures, that just leads to frustration and errors. You need to provide the example data following these instructions, and not via any other method (and not via an attachment either).

[ballardw]

Additionally: A date without a year is not a date.

Personally, and I say this after some extended time in a hospital, why do you not have the date and TIME for each measurement? I know my medical records have such, some at less than 2 hour intervals.

And any interpolation of date to measure, possibly the proper word for what you are asking, is extremely suspect if some measures were taken at 2 hour intervals and other at 48 hour intervals.

[mitrakos]

Hi,

These are home Blood pressure measurements. not Hospital taken ones.

[ballardw]

---

@mitrakos wrote:
Hi,

These are home Blood pressure measurements. not Hospital taken ones.

---

Then someone set a data collection protocol incorrectly.  In effect what you are asking us is to guess, or interpolate, that "time" of entry, especially if you have an interval of 2 days and 11 measurements (or whatever you actually have).

[SASJedi]

Hi, there! Your dates do not contain YEAR information, so it is not possible to create a solution that takes into account a series that spans a year (for example, dates go from Dec 29 - Jan 5). With that caveat, here is some code to get you started:

data have;
   infile datalines dsd dlm='|' truncover;
input ID	Date:$30. Systolic	Diastolic	Pulse	Notes:$50.;
datalines;
1|Start Nov 8 End Nov 10 - 6|109|78|73| 
1|Start Nov 8 End Nov 10 - 5|110|84|79| 
1|Start Nov 8 End Nov 10 - 4|107|78|67| 
1|Start Nov 8 End Nov 10 - 3|114|83|63| 
1|Start Nov 8 End Nov 10 - 2|115|81|58| 
1|Start Nov 8 End Nov 10 - 1|110|77|63| 
2|Start Nov 8 End Nov 9  - 5|114|83|63| 
2|Start Nov 8 End Nov 9  - 4|115|81|58| 
2|Start Nov 8 End Nov 9  - 3|110|77|63| 
2|Start Nov 8 End Nov 9  - 2|115|81|58| 
2|Start Nov 8 End Nov 9  - 1|114|83|63| 
;

data counts;
/*  keep id _start _end _obsperday;*/
  keep id _:;
  set have;
  by id;
  if first.id then do;
     _numObs=input(scan(date,-1,'- '),comma5.);
     _start=input(cats(scan(date,3),scan(date,2),'2020'),date9.);
     _end  =input(cats(scan(date,6),scan(date,5),'2020'),date9.);
     _numdays=intck('days',_start,_end)+1;
     _obsperday=ceil(_numobs/_numdays);
     output;
  end;
run;

data want;
   length id date 8;
   merge have (drop=date) 
         counts;
   by id;
   format date mmddyy10.;
   retain Date _record;
   drop _:;
   if first.id then do;
     Date=_start;
     _record=0;
   end;
   _record+1;
   if _record <= _obsperday then output;
   else do;
      _record=1;
      Date+1;
      output;
   end;
run;