How to merge rows under the same ID with additional conditions?

[LarissaW]

Hi,

I'd like to know, is there a way to merge data like what is shown in the figure? I have several observations under the same ID. I want to merge data based on both ID and Type. For the new data highlighted in the expected table, 13 is from the length 10+3, and 54 is from the weighted average of the two observations of Type 1 under ID 1 [(10x3+67x10)/(10+3)]. I also have other data in the database that need to be cleaned like this, so I would like to know if there are codes that I can apply to bulk change the structure of the dataset to the expected one.

Thank you!

[PaigeMiller · Accepted solution]

Why would you want to re-arrange this data at all? What can you do with the wide data set that you cannot do with the long data set?

Sums, averages and weighted averages can all be computed from the original data without any re-arranging of the data.

proc summary data=have nway;
    class id type;
    var year;
    var dose/weight=year;
    output out=sum_dose sum(year)=year mean(dose)=dose;
run;

[LarissaW]

Hi, thank you for your reply. I have to merge it to a single ID because I will merge this data with another database and use the merged dataset to do a Cox proportional analysis. I thought that the Cox model doesn't allow multiple observations with the same ID.

[PaigeMiller]

As stated above, you can still do the calculations without re-arranging.

[LarissaW]

I'm still confused. If I want to merge two datasets, I think I should sort the data by ID first. The long data multiple observations with the same ID, then how is it possible to be merged with another dataset? Also, I think each row will be calculated as a sample, so if I have multiple rows with the same ID, the Cox proportional model will miscalculate the wrong categories.

[ballardw]

---

@LarissaW wrote:

I'm still confused. If I want to merge two datasets, I think I should sort the data by ID first. The long data multiple observations with the same ID, then how is it possible to be merged with another dataset? Also, I think each row will be calculated as a sample, so if I have multiple rows with the same ID, the Cox proportional model will miscalculate the wrong categories.

---

You haven't shown a second data set to involve any "merge". So you need to show that data set as well before any "merge" is discussed. And then what the appearance of that resulting merged data would look like.