How do i Transpose column into Rows?

[sameer112217]

Need help, I have an input table like this. I want to transpose the values in agriemail and sigrimeial against cluster and cluster1 in rows from columns

Input Table
CLUSTER	CLUSTER1		AGRIEMAIL	SIGRIEMAIL
AJMER	AGRI PROD		sameer1122@yahoo.com	hardik@yahoo.com
AJMER	AGRI PROD		amit1122@yahoo.com	umran@yahoo.com
AJMER	AGRI PROD		nikhil@yahoo.com	rahul@yahoo.com
SURAT	SIGRI GOLD		rajesh@yahoo.com	virat@yahoo.com
SURAT	SIGRI GOLD		Kranti@yahoo.com	rohit@yahoo.com
SURAT	SIGRI GOLD		Nitin@yahoo.com	shami@yahoo.com

Want output like this

CLUSTER	CLUSTER1		Email1	Email2	Email3
AJMER	AGRI PROD		sameer1122@yahoo.com	amit1122@yahoo.com	nikhil@yahoo.com
AJMER	AGRI PROD		sameer1122@yahoo.com	amit1122@yahoo.com	nikhil@yahoo.com
AJMER	AGRI PROD		sameer1122@yahoo.com	amit1122@yahoo.com	nikhil@yahoo.com
SURAT	SIGRI GOLD		virat@yahoo.com	rohit@yahoo.com	shami@yahoo.com
SURAT	SIGRI GOLD		virat@yahoo.com	rohit@yahoo.com	shami@yahoo.com
SURAT	SIGRI GOLD		virat@yahoo.com	rohit@yahoo.com	shami@yahoo.com

It can be email 1-50. Tried Proc Transpose but it did not work

Proc Transpose data = sample out=output

by  cluster cluster1;

id email1

var agriemail sigriemail;

run;

Not getting desired output.

[Tom · Accepted solution]

You don't show any variable named EMAIL1 which the posted code is trying to use as the source of the NAME of new variables.  But it does not look like you need or want one.

Instead use the PREFIX= option.

proc Transpose data = sample out=output prefix=email ;
  by  cluster cluster1;
  var agriemail sigriemail;
run;

 So you will get two observations per CLUSTER, CLUSTER1 combination with as many EMAIL columns as needed.

[PaigeMiller]

Is the issue that when CLUSTER1='AGRI PROD' you want to transpose Agriemail and when CLUSTER1='SIGRI GOLD' then you want to transpose SIGRIEMAIL? Are there more than two values of CLUSTER1? 

In the output ,why are there three identical rows for AJMER and three identical rows for SURAT? Why don't you want one row for AJMET and one row for SURAT?

@sameer112217 

We usually require data to be presented as working SAS data step code (instructions). We can't work with data presented as screen captures. Please provide data in an acceptable form. Thanks!

[mkeintz]

I have no idea of what reorganization rules you want to apply.  For one cluster/cluster1 combination you transpose agriemail, for the other combinations you transpose sigriemail.  What is the rule for making this distinction?  If it is the value of cluster1, then how many possible values of cluster1 are there?

Why are you producing duplicate records, given all the information is contained in one output record?  If you start out with 10 records for a given cluster/cluster1, do you intend to populate 10 variables, and also 10 duplicate records?