topic Re: Lag function with partition by in SAS Programming

Lag function with partition by

roh910 — Sat, 06 May 2017 22:09:46 GMT

Hi there,

I have the following dataset structure

Facility Appointment_date Patient_id

a 5/4/2013 123

a 5/5/2013 123

a 5/5/2013 232

a 5/15/2013 123

I want to find out the difference in successive appointment_dates for patients who had multiple visits. The structure of the output should look as follows

Facility Appointment_date Patient_id Lag Diff

a 5/4/2013 123 . .

a 5/5/2013 123 5/4/2013 1

a 5/5/2013 232 . .

a 5/15/2013 232 5/5/2013 10

I know that I can get the result by using lag function in SQL with the following query

select *, lag(appointment_date,1) over (partition by patient_id order by appointment_date) as lag from table1;

Kindly let me know if there is a way I can do the same using SAS code?

Re: Lag function with partition by

art297 — Sat, 06 May 2017 22:26:25 GMT

Sounds like you are looking for something like:

data have;
  input Facility $ Appointment_date mmddyy10. Patient_id;
  format Appointment_date mmddyy10.;
  cards;
a  5/4/2013  123
a  5/5/2013  123
a  5/5/2013  232
a  5/15/2013  232
;

data want;
  set have;
  format lag mmddyy10.;
  by facility Patient_id;
  lag=ifn(first.Patient_id,.,lag(Appointment_date));
  dif=ifn(first.Patient_id,.,dif(Appointment_date));
run;

Art, CEO, AnalystFinder.com

Re: Lag function with partition by

roh910 — Sat, 06 May 2017 23:46:45 GMT

Thanks. It's working!!

Re: Lag function with partition by

art297 — Sat, 06 May 2017 23:49:37 GMT

Then mark the question as 'solved'