DATA Step, Macro, Functions and more

Lag function with partition by

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

Lag function with partition by

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?

 

 

 


Accepted Solutions
Solution
‎05-06-2017 11:29 PM
PROC Star
Posts: 7,356

Re: Lag function with partition by

Then mark the question as 'solved'

View solution in original post


All Replies
PROC Star
Posts: 7,356

Re: Lag function with partition by

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

New Contributor
Posts: 2

Re: Lag function with partition by

Thanks. It's working!!

Solution
‎05-06-2017 11:29 PM
PROC Star
Posts: 7,356

Re: Lag function with partition by

Then mark the question as 'solved'

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 210 views
  • 0 likes
  • 2 in conversation