How to move data from rows into colums...

[surgicalstrike]

Hi everyone;

I'm trying to minimize a massive database that I need to analyze for a research project. Right now, the data I have is in this format, where there are multiple entries for one person (PatientLinkCode) that have a procedure code (CCPXCODE) and a date associated with it (ProcedureDate). What I want to do is create a query (and then ultimately a table after the query is good and is what I want) that moves the data from rows--specifically the CCPXCODE field (the procedure codes) to columns. The image below shows how the data is setup right now:

What I'd like is for there to be one patient per line and all the variable CCPXCODEs to be in the columns as well with the date right after. For example, with the PatientLinkCode above of 140218, I'd like the data to look like this:

AccessIDCode PatientLinkCode 28.4A ProcedureDate(for that CCPXCODE) 28.74B ProcedureDate 28.72B ProcedureDate THEN THE REST OF FIELDS AS LISTED ABOVE IN THAT ORDER.

Under the codes (for example 28.4A above) there would be a 1 indicating yes for that code and the resulting date right next to it. For any record that doesn't have a CCPX code, then there would just be nothing (ie. null).



There are about 30 CCPXCODES repeated in the database with variable dates per procedure. If there are multiple CCPXCODEs per patient (for example, if there was a 28.72B on 8/18/2004 and then another one on 9/17/2004) then I would want that captured as well in a column.

Is there anyway to do this with a query and then run a maketable query to get the data the way I want? It has to be this way since after it is formatted this way it needs to be exported for a SAS stats routine. This is why it has to be binarized (either a 1 or a NULL in the field) for each record.

I have tried doing this with crosstab but am only able to get 3 columns out of it instead of all the ones I need.

Any help would be appreciated.

[CJ_London]

I have tried doing this with crosstab but am only able to get 3 columns out of it instead of all the ones I need.

Just some random thoughts/questions:

Why doesn't a crosstab do what you want? You can have as many row headings as you want, plus your 30 CCPXCODEs

THEN THE REST OF FIELDS

if you are creating a table you can set the order anyway you like

Can't SAS do this for you from the data as provided? In my experience, all it will do is convert it back to a normalised structure anyway

Have you considered exporting to excel and pivoting there?

This is why it has to be binarized (either a 1 or a NULL in the field) for each record.

I don't understand what you mean here when you are implying you need a date - or are there two columns for each CCPXCODE?

[surgicalstrike]

Hi Ajax, thanks for the quick reply.

Crosstab only allows me to do 3 columns, can't do more than that. Perhaps I'm not writing the query correctly.

SAS can extract data, but I want to ensure that the data is binarized correctly before inputting to SAS. The statistician that we are working with will not do any modification of the data that is provided (researcher integrity) ONLY the data that we provide, so I have to have it done before it goes to SAS. Also, there may be some codes that we want to remove before we analyze it.
I have considered exporting to Excel, but the data set includes over 750,000 records for over 10 years of procedures. Excel chunks hard on this data even on my system.
For each CCPXCODE, there is a corresponding date. I would want the code from the CCPXCODE field AND the resulting date next to it, so essentially yes there are 2 pieces of data that need manipulating.

Hope this clears up my original question.

J

[CJ_London]

Crosstab only allows me to do 3 columns, can't do more than that. Perhaps I'm not writing the query correctly.

Based on the data you provided, a crosstab would have produced whatever columns you wanted to show (e.g. AccessIDCode, PatientLinkCode. SurgeonID, FacilityLocation, etc) plus a further eleven columns for CCPXCODE, so sounds like you are not writing it correctly (or have filtered to one row for testing) - suggest you post the SQL you tried to use. However if you want two columns for each CCPXCODE, one with a binary value and one with a date then you will need two crosstabs which you then combine in a third query.

The statistician that we are working with will not do any modification of the data that is provided (researcher integrity)

Sounds like an excuse to me - if you added another CCPXCODE then they will need to completely rewrite their analysis - but then, someone else is paying the bill for the extra work (sorry, just me being cynical, everyone tries to make their job easier). After all, you will be exporting the same number of rows as you have at the moment, with (based on the current data), 22 columns related to CCPXCODE, but only 2 populated per row

ONLY the data that we provide

Precisely - they would not be changing it, all you are doing is moving a date to a different column based on the CCPXCODE and adding another column which is effectively saying 'there is a date in the next column'.

Also, there may be some codes that we want to remove before we analyze it.

easily done with a filter

Based on the data provided, this code should give you the 'procedure date' crosstab - I've used your names so you should be able to just copy and paste, save as QCB1

TRANSFORM First(YAGCombinedBackup.ProcedureDate) AS FirstOfProcedureDate
SELECT YAGCombinedBackup.AccessIDCode, YAGCombinedBackup.PatientLinkCode, YAGCombinedBackup.ICD9XCODE1, YAGCombinedBackup.ICD9XCODE2, YAGCombinedBackup.ICD9XCODE3, YAGCombinedBackup.DateOfFirstYAG, YAGCombinedBackup.SurgeonID
FROM YAGCombinedBackup
GROUP BY YAGCombinedBackup.AccessIDCode, YAGCombinedBackup.PatientLinkCode, YAGCombinedBackup.ICD9XCODE1, YAGCombinedBackup.ICD9XCODE2, YAGCombinedBackup.ICD9XCODE3, YAGCombinedBackup.DateOfFirstYAG, YAGCombinedBackup.SurgeonID
PIVOT YAGCombinedBackup.CCPXCODE

This gives you the 'binary' crosstab - save as QCB2

TRANSFORM First(Abs([ProcedureDate]=[ProcedureDate])) AS Expr1
SELECT YAGCombinedBackup.AccessIDCode
FROM YAGCombinedBackup
GROUP BY YAGCombinedBackup.AccessIDCode
PIVOT YAGCombinedBackup.CCPXCODE

And this combines them - you'll need to add all the relevant CCPXCODE codes, I've just done two

SELECT QCB1.AccessIDCode, QCB1.PatientLinkCode, QCB1.ICD9XCODE1, QCB1.ICD9XCODE2, QCB1.ICD9XCODE3, QCB1.DateOfFirstYAG, QCB1.SurgeonID, QCB1.[28.4 A], QCB2..[28.4 A], QCB1.[28.74B], QCB2.[28.74B]
FROM QCB1 INNER JOIN QCB2 ON QCB1.AccessIDCode = QCB2.AccessIDCode;