topic Re: How to aggregate date data in different line in the same line? in SAS Programming

How to aggregate date data in different line in the same line?

MJLEE — Fri, 14 Dec 2018 09:15:02 GMT

Hi everybody!

I'm trying to aggregate a variable, between two specific dates for each patient Id. So Imagine I have a database with two columns: Patient ID, Date. I want to calculate the difference between the dates that patient visit a clinic. It's a panel data. I have multiple observations for each patient ID from 2007 to 2017. I want to aggregate each variable's value between specific dates for each patient ID.

If that helps, I can you an example;

patientID / date

111/20071207

111/20080102

111/20080103

111/20080201

22/20160303

22/20160315

22/20160316

22/20160507

22/20160601

22/20160616

22/20160620

3/20171010

3/20271018

into

patientID / date1 / date2/ date3/ date4/ date5/ date6/ date7/ ...

111/20071207/20080102/20080103/20080201/20160507/./...

22/20160303/20160315/20160316/20160620/././././..

3/20171010/20271018 ./././././ ...

Is it impossible to make data like this type?

please help me to solve the problem.

Thank you!

Re: How to aggregate date data in different line in the same line?

RW9 — Fri, 14 Dec 2018 09:25:52 GMT

Whilst it is not impossible to create your want dataset - you can use a simple proc tranpose to get it - I would recommend that it is not the best method. Transposed data is only useful for output reports so humans can read it. Much simpler to code with long data. You have not however provided any information about your problem. Follow the posting a question guidance. Post test data in the form of a datastep using a code window (its the {i} above post area):
https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712

This is so we have something to work with. Then show what you want out at the end. Explain your problem showing any errors/warnings log parts, or code you might have already done, and explain what does not work.

Re: How to aggregate date data in different line in the same line?

Kurt_Bremser — Fri, 14 Dec 2018 09:36:13 GMT

@MJLEE wrote:

Hi everybody!

I'm trying to aggregate a variable, between two specific dates for each patient Id. So Imagine I have a database with two columns: Patient ID, Date. I want to calculate the difference between the dates that patient visit a clinic. It's a panel data. I have multiple observations for each patient ID from 2007 to 2017. I want to aggregate each variable's value between specific dates for each patient ID.

If that helps, I can you an example;

patientID / date

111/20071207

111/20080102

111/20080103

111/20080201

22/20160303

22/20160315

22/20160316

22/20160507

22/20160601

22/20160616

22/20160620

3/20171010

3/20271018

into

patientID / date1 / date2/ date3/ date4/ date5/ date6/ date7/ ...

111/20071207/20080102/20080103/20080201/20160507/./...

22/20160303/20160315/20160316/20160620/././././..

3/20171010/20271018 ./././././ ...

Is it impossible to make data like this type?

please help me to solve the problem.

Thank you!

Stay with the long format, as @RW9 suggested, and use lag() to calculate date differences:

data have;
infile cards dlm='/';
input patient_id $ visit_date :yymmdd8.;
format visit_date yymmddd10.;
cards;
111/20071207
111/20080102
111/20080103
111/20080201
22/20160303
22/20160315
22/20160316
22/20160507
22/20160601
22/20160616
22/20160620
3/20171010
3/20271018
;
run;

data want;
set have;
by patient_id;
date_diff = visit_date - lag(visit_date);
if first.patient_id then date_diff = .;
run;

proc print data=want noobs;
run;

Result:

patient_                  date_
   id       visit_date     diff

  111       2007-12-07        .
  111       2008-01-02       26
  111       2008-01-03        1
  111       2008-02-01       29
  22        2016-03-03        .
  22        2016-03-15       12
  22        2016-03-16        1
  22        2016-05-07       52
  22        2016-06-01       25
  22        2016-06-16       15
  22        2016-06-20        4
  3         2017-10-10        .
  3         2027-10-18     3660

Re: How to aggregate date data in different line in the same line?

MJLEE — Fri, 14 Dec 2018 14:26:00 GMT

Thank you for your help! This is the solution that I'm finding!