topic Re: Proc SQL and Arrays in SAS Programming

Proc SQL and Arrays

niam — Mon, 21 Sep 2015 14:55:56 GMT

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

Re: Proc SQL and Arrays

Reeza — Mon, 21 Sep 2015 15:12:26 GMT

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

Re: Proc SQL and Arrays

PGStats — Mon, 21 Sep 2015 17:23:11 GMT

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)

Re: Proc SQL and Arrays

niam — Thu, 24 Sep 2015 17:32:55 GMT

This works! Thank you very much