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-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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