topic Separate and Pivot data in SAS Programming

Separate and Pivot data

avpeshwe — Thu, 23 Aug 2018 22:34:06 GMT

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

Re: Separate and Pivot data

novinosrin — Thu, 23 Aug 2018 22:37:54 GMT

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

Re: Separate and Pivot data

Reeza — Thu, 23 Aug 2018 22:40:01 GMT

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

Re: Separate and Pivot data

avpeshwe — Thu, 23 Aug 2018 22:43:55 GMT

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.

Re: Separate and Pivot data

Reeza — Thu, 23 Aug 2018 22:47:20 GMT

@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.

Re: Separate and Pivot data

avpeshwe — Thu, 23 Aug 2018 23:57:05 GMT

Hello Reeza,

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

Thanks.

Re: Separate and Pivot data

Reeza — Fri, 24 Aug 2018 00:45:37 GMT

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.

Re: Separate and Pivot data

avpeshwe — Wed, 29 Aug 2018 02:37:03 GMT

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