How to create variables in a new workset that reference multiple other worksets?

[jhathaway]

Hi there,

I am a Grad student and I am working with administrative health data. I am working with 4 different databases that will allow me to locate a patient based on which database they are in. I am wanting to create a new data set and all it would have is all the patient IDs as observations and then the variables would be dates 1 through to 365 of a given year. The trick is that I want to write a program that will scan the 4 databases for each day variable and if a patient ID is found in that database on the given day a database specific flag will be returned to the set in the correct observation. Ideally this gives me a data set that I can see any time over a year a patient has used a health service from the 4 different data bases. Is this possible?

More basic than that I am curious how I can transpose a given data set. As a hypothetical the data I am working with has patient's visits in chronological order as observations. Something like this:

Patient ID   Visit Date

1                     1/25/2015

1                      5/26/2016

4                     6/14/2013

5                     7/7/2010

5                      9/15/2015

Now what I want to do is create a data set where each observation is a patient and the variables are the different visit dates. Something like this:

Patient ID     1                  2   

1            1/25/2015        5/26/2016

4              6/14/2013

5                7/7/2010          9/15/2015

Is it possible to create a new data set that references the old one to sort of transpose the visit dates?

Thank you for any input. It is much valued.

[ballardw]

First thing, are those 4 other databases going to have records being updated constantly that you need to see?

How will you read that data?

You may be able to create data views for each of those external databases. Basic code would look like:

Data datafile1 / view=datafile1;

<statements that would create the body of a data set>

Infile and input or set. I have not done this with CONNECT to remote databases though.

Then write code that aligns and merges those 4 views. The result would then refresh from the external source assuming everything goes well when ever it is used.

Without knowing what you will do with the result I can't make suggestions as to how to implemet more than that.

I suspect that data in the form of

Patientid Date DBSource <medical information fields> may be of more use.

Make sure the Date variable(s) are SAS date values and with combined data then you query that data with things like: where date < '01Jul2016'd or between.

I suggest that you seriously consider what you would do with that transposed data. Trying to match that to anything is going to be a nightmare.

[PGStats]

You could assemble your data using SQL:

proc sql;
create table allData as
select "A" as db, patientId, visitDate from A
union all
select "B" as db, patientId, visitDate from B
union all
select "C" as db, patientId, visitDate from C
union all
select "D" as db, patientId, visitDate from D
order by patientId, visitDate, db;
quit;

Transposing might be useful for reporting but avoid it for any further data processing.