Lag function with partition by

[roh910]

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?

[art297]

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

[roh910]

Thanks. It's working!!

[art297 · Accepted solution]

Then mark the question as 'solved'