topic proc sql for vertical to horizontal data set conversion in SAS Programming

proc sql for vertical to horizontal data set conversion

sms1891 — Mon, 16 Jul 2018 21:57:32 GMT

Dear SAS users,

I do have a large data set with multiple patients (Pt_ID). There can be multiple entries for a single patient (each entry is recorded by the same Pt_ID). For each Pt_ID, same specimen is collected at different times (datetime variable - Collection_Date). However, for a given Collection_Date for a given Pt_ID, sometimes 5 tests (variable CULTURE_TYPE) are run and for the same Pt_ID for the next collection_time, there may be 3 tests (CULTURE_TYPE) done. CULTURE_TYPE are of 7 types (Type1, Type2, Type3...Type7). I want to rearrange the data set horizontally from vertical format, so that for each Pt_ID, for a unique Collection_Date, I will have CULTURE_TYPE1, CULTURE_TYPE2, CULTURE_TYPE3...etc.

Also another twist for this data set, for a given Type of CULTURE_TYPE (Type1 or Type2 or..Type7), there may be one bacteria that is isolated or two bacteria isolated (variable Organism). For example, If there are two organisms identified for a Type1, they have same Accession_No. Each Type has different Accession_no's. For example Type1 will have a unique Accession_no, Type2 will have another Accession_no, Type3....etc.

Result for each organism will be (Yes/ NO = Y/N), captured as a separate variable (Result_Uc).

So basically I need this data set to be in horizontal form for a unique Collection_Date and Culture_Type and Organism transposed horizontally.

Please see the attached excel sheet for the data set and variables. I highlighted duplicate accession_no's in RED.

Thank you in advance for all the help!

Re: proc sql for vertical to horizontal data set conversion

PaigeMiller — Mon, 16 Jul 2018 22:07:37 GMT

First, I think requiring PROC SQL is the wrong thing here, and so I will make no attempt to find an SQL solution. Use PROC TRANSPOSE with a BY statement.

Secondly, I think leaving the data in the format it is in and not transposing it will make the analysis easier. Of course, I don't know what type of analysis you plan to do, but rarely is a wide format superior to a long format.

Re: proc sql for vertical to horizontal data set conversion

ballardw — Mon, 16 Jul 2018 22:10:40 GMT

Your topic title seems to imply that you only want an SQL solution. Is that actually case? It may be possible but I'm afraid that the code could get voluminous.

Can you tell use exactly what you will be doing that requires the wide format you describe? It may be easier to leave the data in the current form a use a slightly different process than currently envisioned.

And you really should provide what you expect for the result as a table as I am not sure I can get a clear grasp on CULTURE_TYPE1, CULTURE_TYPE2, CULTURE_TYPE3 (how many would there be??? another issue with "wide" data) and then adding an unknown number of Result_Uc varaibles as well.