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!
@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
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-dat...
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.
@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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.