Hi,
I have two datasets about a group of patients. In the first dataset, called medhis, I have the medical history of patients which includes the dates when a lab or radiology test was ordered for a patient. Something like this:
patientID Result Date
001 lab 05/04/2010
001 lab 06/07/2011
001 rad 07/07/2012
002 lab 05/02/2010
. .
... more data lines ......
. .
205 rad 08/21/2015
In the second dataset, called consent, I have the dates and types of patient consent, but the structure is different. Something like the following. Note that the data from a patient only appears in one row and not all patients provide multiple consents
patientID Consnet1 Date1 Consent2 Date2 Consent3 Date3
001 Y 06/04/2010 N 08/08/2012 . .
002 Y 08/05/2011 . .
. .
... more data lines ......
. .
205 N 08/21/2015
I want to create a table in with a structure like the medhis table with a variable called totdoc which shows the total number medical records that are ordered for each patient from the beginning until each consent date. something like this:
patientID ConsentType ConsentDate totdoc
001 Y 06/04/2010 1
001 N 08/08/2012 3
002 Y 08/05/2011 1
. .
... more data lines ......
. .
205 N 08/21/2015 1
I think I somehow should use arrays within a proc sql, but I don't know how.
I appreciate your help in advance
You need both, but not together :
data consentList;
set consent;
array d{*} date:;
array c{*} consent:;
do i = 1 to dim(d);
consentType = c{i};
consentDate = d{i};
if not missing(consentDate) then output;
end;
drop i;
run;
proc sql;
create table want as
select patientID, consentType, consentDate,
(select count(*) from medhis where patientID=a.patientID and date <= a.consentDate) as totDoc
from consentList as a;
quit;
(untested)
Arrays don't exist within Proc SQL, I think you're looking for a transpose, via a datastep and array.
Here's a reference that goes through several examples of transposing via a data step.
http://www.ats.ucla.edu/stat/sas/modules/widetolong_data.htm
You need both, but not together :
data consentList;
set consent;
array d{*} date:;
array c{*} consent:;
do i = 1 to dim(d);
consentType = c{i};
consentDate = d{i};
if not missing(consentDate) then output;
end;
drop i;
run;
proc sql;
create table want as
select patientID, consentType, consentDate,
(select count(*) from medhis where patientID=a.patientID and date <= a.consentDate) as totDoc
from consentList as a;
quit;
(untested)
This works! Thank you very much
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.