We have data that is collected on a per patient level at varying time points from the Baseline date (CGI_Rank in file 1 = baseline date). What I'd like to do is "normalize" the data such that no matter what the Baseline date is, each future data point is grouped based on a certain time period (e.g., Months) from the Baseline date.
Thus, if Patient A has a data point on 8/1/18, a second point on 9/5/18, and a third on 10/29/18, the base date of 8/1/18 is used as the initial date, and each point is not the exact month, but instead the number of months from the base date. Thus, second point is saved on M+2, then M+3 and so on. If there are two data points within the same month, the data should be averaged, if there is no data, then it is null.
Not clear if you want to try to script something here or do in a visualisation.
If you want to script a possible step 1 (if I've understood you right) would be to join back the date to itself where CGI_RANK=1;
[SAMPLE DATA FOR QV Group.xls]
(biff, embedded labels, table is Sheet1$);
left join (data)
Date AS CGI_RANK1_Date
Which gives the following;
Step 2 would involve doing a load processing the two dates (Date & CGI_RANK1_Date) … but does the above look like a step to what you want to do?