BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
eabc0351
Quartz | Level 8

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

 

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
SASKiwi
PROC Star

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.

View solution in original post

1 REPLY 1
SASKiwi
PROC Star

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.

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 1 reply
  • 1603 views
  • 0 likes
  • 2 in conversation