Separate and Pivot data

[avpeshwe]

Hello All,

I have two data sets:

Data set 1:

ID Major  Some more fields

1    A

1    B

2    A

2    B

2    C

Data set 2:

ID Minor  Some more fields

1    M

1    N

2    X

2    Y

2    Z

I am looking for something like this:

ID   Major_1  Major_2  Major_3  Minor_1 Minor_2 Minor_3

1       A             B                             M            N

2       A             B                             X             Y           Z

Kindly let me know how shall I proceed.

Thanks in Advance

[novinosrin]

Do you want to ignore "Some more fields" in both the datasets?

[avpeshwe]

No I want some other fields as well. However, I want to create additional columns based on Majors and Minors for single ID. So basically I want a single record for 1 ID.

[Reeza]

---

@avpeshwe wrote:

No I want some other fields as well. However, I want to create additional columns based on Majors and Minors for single ID. So basically I want a single record for 1 ID.

---

How you plan to combine those matters as well. You'll need to provide more details and data that better reflects your actual scenario if you need assistance. 

[avpeshwe]

Hello Reeza,

I have attached an excel file that will better explain what I want.

Thanks.

[Reeza]

I think the link I posted covers that situation, example 2 to be specific. You would run proc transpose on each data set and the use a merge to combine them into one. 

[Reeza · Accepted solution]

Look into PROC TRANSPOSE and MERGE

https://stats.idre.ucla.edu/sas/modules/how-to-reshape-data-long-to-wide-using-proc-transpose/

---

@avpeshwe wrote:

Hello All,

I have two data sets:

Data set 1:

ID Major  Some more fields

1    A

1    B

2    A

2    B

2    C

Data set 2:

ID Minor  Some more fields

1    M

1    N

2    X

2    Y

2    Z

I am looking for something like this:

ID   Major_1  Major_2  Major_3  Minor_1 Minor_2 Minor_3

1       A             B                             M            N

2       A             B                             X             Y           Z

Kindly let me know how shall I proceed.

Thanks in Advance

---

[avpeshwe]

Thanks Reeza. Proc transpose and merge worked perfectly for me!