Date: Thu, 21 Jan 2010 12:58:08 -0800
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Mary <mlhoward@AVALON.NET>
Subject: Re: How to organize Excel data for longitudinal analysis?
Content-Type: text/plain; charset="UTF-8"
The most important thing to emphasize is that you do NOT want a report! Sometimes they will use software that will give you headers every 50 lines or so and that is really, really awful to deal with.
Most likely the data is stored long, so it makes sense to ask for it long, as you really don't want the client to be doing too many transformations (there's help here for the transformations, Peter :-)).
If you have different times, be sure to ask for a date/time stamp on the time. You probably will have to roll up data to patient-date of service level, but I'd try to get the raw data so you can roll it up yourself.
Ask for a file, or spreadsheet of each different kind of data separately, such as lab data, medications, and nursing data.
Lab data might look like this:
ID, Sample(Accession) number, Draw Date/Time, Result Date Time, Test, Result
001, 01, 20080101:12:18, 20080101:01:18,HEMOGLOBIN, 12.2
001, 01, 20080101:12:18, 20080101:01:18,HEMATOCRIT, 10.1
001, 01, 20080101:12:18, 20080101:01:18,WHITE BLOOD CELL COUNT, 12.2
Whereas Medications might look like this:
ID, Date Prescribed, Start Date, End Date, Brandname, Fill Quantity
001, 20080101,20080101, 20080131, MAXALT, 10
Nursing data would be like the lab data, where you have what the test was and what the result was
ID, DATETIME, TEST, RESULT
001, 20080101:12:18, WEIGHT-POUNDS, 118
001, 20080101:12:18, HEIGHT-INCHES, 54
Also, you can wind up with tons of lab data, make sure to tell them it is OK to send a tab-delimited file rather than an Excel file if it is too long.
--- peterflomconsulting@MINDSPRING.COM wrote:
From: Peter Flom <peterflomconsulting@MINDSPRING.COM>
Subject: How to organize Excel data for longitudinal analysis?
Date: Thu, 21 Jan 2010 15:42:15 -0500
A miracle occurred! A client came to me to ask HOW BEST to send me the data. Zowie!
Now I have to answer her.
The data is fairly complicated. She is gathering data on about 60 babies who were in the NICU; data includes ID, various DVs at different times, and various IVs at different times. Times are not consistent, because bloods were drawn on different days, and there are not even a consistent number of times ... some babies have c. 10 times, others only 2 or 3.
She even gave me what she has so far on one baby, but I don't think SAS-L will let me attach it.
So, if someone has dealt with a lot of this type of data, and wants to save me from all sorts of SQL or transpose (and save the list from my questions!) just let me know
Peter L. Flom, PhD
Website: http://www DOT statisticalanalysisconsulting DOT com/