topic Re: How do i Transpose column into Rows? in SAS Programming

How do i Transpose column into Rows?

sameer112217 — Thu, 30 Jun 2022 17:49:51 GMT

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.

Re: How do i Transpose column into Rows?

Tom — Thu, 30 Jun 2022 18:29:37 GMT

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.

Re: How do i Transpose column into Rows?

PaigeMiller — Thu, 30 Jun 2022 18:07:09 GMT

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!

Re: How do i Transpose column into Rows?

mkeintz — Thu, 30 Jun 2022 18:47:44 GMT

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?