DATA Step, Macro, Functions and more

Proc SQL and Arrays

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 84
Accepted Solution

Proc SQL and Arrays

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

 

 


Accepted Solutions
Solution
‎09-25-2015 06:23 AM
Respected Advisor
Posts: 4,649

Re: Proc SQL and Arrays

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)

PG

View solution in original post


All Replies
Super User
Posts: 17,829

Re: Proc SQL and Arrays

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

Solution
‎09-25-2015 06:23 AM
Respected Advisor
Posts: 4,649

Re: Proc SQL and Arrays

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)

PG
Frequent Contributor
Posts: 84

Re: Proc SQL and Arrays

This works! Thank you very much

☑ This topic is SOLVED.

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

Discussion stats
  • 3 replies
  • 220 views
  • 0 likes
  • 3 in conversation