@alaxman,
Excellent! Good job!
The hop macro might be a bit of overkill for this application, but it's a nice piece of code to having a working example of. You never know when it might be handy to have a read ahead.
@ChrisNZ,
I think the issue is that @alaxman wanted record two on the original file in effect updated in place. I don't think he wanted any additional rows or columns, at least that's my understanding.
While @alaxman was sorting out the hop macro, I coded an alternative (see code below my "signature"). Basically, I figure out what the revisions should be, create a little dataset to contain said revisions, and then re-join the revisions dataset with the original, updating row 2 which gives me the following results which I think is what @alaxman was after. Note that DaySupp on record 2 has been updated.
My code is below. Note that this code is a bit on the primitive side. It doesn't for example (yet) take into account that there might be more than one Patient_ID present in the data. I'll leave the code as is in case someone might find an alternative technique interesting (and since I've already written it), but my main point is the results set, above, with the updated DaySupp on row 2.
Jim
DATA Drug_Data;
INFILE Datalines DSD DLM='09'x MISSOVER;
INPUT Patient_ID $
SvcDate : ANYDTDTE8.
DaySupp
ProdNme : $25.
;
FORMAT SvcDate mmddyys10.;
Datalines;
123 9/29/14 7 ZUBSOLV
123 10/2/14 30 ZUBSOLV
123 10/9/14 30 NALTREXONE HCL
;
RUN;
DATA Partial_Update_Drug_Data Revisions(KEEP=Patient_ID SvcDate DaySupp);
DROP _:;
SET Drug_Data;
LENGTH _Prior_ID $8;
LENGTH _Prior_Drug $25.;
RETAIN _Prior_ID ' ';
RETAIN _Prior_Date 0;
RETAIN _Prior_Supp 0;
RETAIN _Prior_Drug ' ';
LENGTH _Save_Date 8;
LENGTH _Save_Supp 8;
IF ProdNme = _Prior_Drug THEN
DO;
SvcDate = INTNX('DAYS', _Prior_Date, _Prior_Supp);
END;
ELSE
IF ProdNme IN ("VIVITROL", "REVIA", "NALTREXONE HCL", "NALTREXONE") THEN
DO;
_Save_Date = SvcDate;
_Save_Supp = DaySupp;
DaySupp = INTCK('DAYS', _Prior_Date, SvcDate);
IF DaySupp ^= _Prior_Supp THEN
DO;
SvcDate = _Prior_Date;
OUTPUT Revisions;
SvcDate = _Save_Date;
DaySupp = _Save_Supp;
END;
END;
_Prior_ID = Patient_ID;
_Prior_Date = SvcDate;
_Prior_Supp = DaySupp;
_Prior_Drug = ProdNme;
OUTPUT Partial_Update_Drug_Data;
RUN;
PROC SQL NOPRINT;
CREATE TABLE Full_Update_Drug_Data AS
SELECT Data.Patient_ID
,Data.SvcDate
,COALESCE(Rev.DaySupp, Data.DaySupp) AS DaySupp
,Data.ProdNme
FROM Partial_Update_Drug_Data Data
LEFT JOIN Revisions Rev
ON Data.Patient_ID = Rev.Patient_ID
AND Data.SvcDate = Rev.SvcDate
;
QUIT;
... View more