Possible to merge long format data and wide format data

[eabc0351]

Hello,

          I have 15 Excel workbooks that need merging. The files are mother and baby healthcare utilization data that have been requested from EHR, hospital discharge and claim data. The data in the workbook are in different formats: some wide format, some long. I've not encountered this type of merge before and could use some assistance.

         In all the workbooks, there are four variables to link on: mother_study_ID, mother_visit_ID, child_study_ID, and child_visit_ID. Not all variables are in all workbooks. Most mom and child workbooks have the visit_ID identifiers, which are in long format.

        Question for step 1 is, can I somehow keep everything as is and merge the wide and long data? (proc sql?)

Or, do I need to change all data that is currently in long format to wide in order to merge? (proc transpose?) I need to have long format for the analysis.

     Thanks for your assistance. I'm super green so any information you provide is greatly appreciated.

E.Charro

[SASKiwi · Accepted solution]

What do you want to do with the data after combining it? If you are wanting to use SAS analysis procedures then "long format" is preferable. Also is this a one-off task or will you be repeating it on a regular basis?

If this is a one-off, then it probably would be more efficient to manually tidy the data in Excel first before importing this into SAS as it will save a lot of coding effort. On the other hand if you are going to have to keep doing this it may be preferable to have a custom import for each workbook that gets the data into a common format before merging. It is important to get column names and lengths consistent.