Proc SQL and Arrays

[niam]

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

[Reeza]

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

[PGStats · Accepted solution]

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)

[niam]

This works! Thank you very much