Difference between multiple rows in same column in SAS/PROC SQL

Question

I have a dataset temp1 as:

ID	Drug	lob	Timestamp
123	acetam	counter	01JAN22 04:25:17
123	acetam	counter	01JAN22 09:15:13
123	acetam	prescr	02JAN22 15:05:25
123	acetam	counter	03JAN22 23:28:05
234	tylenol	counter	11JAN22 18:12:39
345	aztyr	counter	03FEB22 16:11:19
345	aztyr	counter	03FEB22 16:15:20

for the same ID, Drug, lob and the old timestamp create a flag as 'Yes'
the second record of same ID, Drug, lob will also have a flag of 'Yes' when the timestamp is greater than 5 days compared to timestamp of previous entry.

expected output

ID	Drug	lob	Timestamp	flag
123	acetam	counter	01JAN22 04:25:17	Yes
123	acetam	counter	01JAN22 09:15:13	No
123	acetam	prescr	02JAN22 15:05:25	Yes
123	acetam	counter	11JAN22 23:28:05	Yes
234	tylenol	counter	11JAN22 18:12:39	Yes
345	aztyr	counter	03FEB22 16:11:19	Yes
345	aztyr	counter	03FEB22 16:15:20	No

My Code:

create table temp2 as
select id, drug,lob, datepart(timestamp)as timestamp format=mmddyy10.
from temp1
order by id, drug,lob
quit;

data temp3;
set temp2;
by id, drug,lob
diff=timestamp-lag(timestamp);
if first.id and first.drug and first.lob  then do diff=.;end;
run;

I am first trying to calculate the difference between dates so that I can create a flag, but for the 3rd record for an lob='prescr' the diff I am getting is 1 but since the lob is different from the previous records this has to be considered as new record and flag has to 'yes'. I am stuck here and not able to move. Can anyone help?

deristnochda · Accepted Answer · 2022-02-06 11:34:22Z

1

I recommend reading up on BY group processing: https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/lrcon/n01a08zkzy5igbn173zjz82zsi1s.htm

Example 1 illustrates how the first. and last. variables are set.

If I understand correctly you probably want to do something like this

data temp3;
  set temp2;
  by id drug lob;
  diff = timestamp - lag(timestamp);
  if first.lob or diff > 5 then
    flag = 'yes';
  else
    flag = 'no';
run;

answered Feb 6, 2022 at 11:34

deristnochda

5854 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Difference between multiple rows in same column in SAS/PROC SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related