topic Proc Transpose in SAS Programming

Proc Transpose

JJP1 — Mon, 05 Aug 2019 12:03:56 GMT

hi,iam trying to convert the data below to tranpose in SAS DI,but iam not getting what iam expecting.please help.

Sample data input to transpose:

Value	NAME	FILE1	Status
SSSSS	567Q44104	DADA	Not Applicable
SSSSS	20726910		Accepted
Banking	567Q44104	DADA	Not Applicable
AAG Portf	567Q44104	DADA	Not Applicable
AAG Portf	S68855kl		Not Applicable
Failed	S68855kl		Not Applicable
Cache	S68855kl		Not Applicable
AAG Txn	567Q44104	DADA	Not Applicable
AAG Txn	S68855kl		Not Applicable
AAG Txn	20726910		Accepted
ADF	20718435		Accepted
ADG	20726910		Accepted
CXE	S68855kl		Not Applicable

After transpose sample output as below :

NAME	Value1	Value2	Value3	Value4	Value5	FILE1	Status
567Q44104	SSSSS	Banking	AAG Txn			DADA	Not Applicable
20726910	SSSSS	AAG Txn	ADG				Accepted

i was using below ,but not working as expected.please help.

PROC SORT
	DATA=WORK.TEST
	OUT=WORK.SORTTempTableSorted
	;
	BY NAME;
RUN;
PROC TRANSPOSE DATA=WORK.SORTTempTableSorted
	OUT=TRNSTRANSPOSED
	PREFIX=VALUE;
	BY NAME;
	VAR VALUE;
	COPY STATUS FILE1;
RUN; QUIT;

Re: Proc Transpose

Ksharp — Mon, 05 Aug 2019 12:24:48 GMT

You are very close.

data test;
infile cards expandtabs truncover;
input  Value $	NAME: $20.	FILE1 $	Status $20.;
cards;
SSSSS	567Q44104	DADA	Not Applicable
SSSSS	20726910	 .	Accepted
Banking	567Q44104	DADA	Not Applicable
AAGPortf	567Q44104	DADA	Not Applicable
AAGPortf	S68855kl	. 	Not Applicable
Failed	S68855kl	 .	Not Applicable
Cache	S68855kl	 .	Not Applicable
AAGTxn	567Q44104	DADA	Not Applicable
AAGTxn	S68855kl	 .	Not Applicable
AAGTxn	20726910	. 	Accepted
ADF	20718435	 .	Accepted
ADG	20726910	 .	Accepted
CXE	S68855kl	 .	Not Applicable
;

PROC SORT
	DATA=WORK.TEST
	OUT=WORK.SORTTempTableSorted
	;
	BY NAME FILE1 	Status;
RUN;
PROC TRANSPOSE DATA=WORK.SORTTempTableSorted
	OUT=TRNSTRANSPOSED
	PREFIX=VALUE;
	BY NAME FILE1 	Status;
	VAR VALUE;
RUN;

Re: Proc Transpose

JJP1 — Mon, 05 Aug 2019 14:41:05 GMT

Thanks @Ksharp for kind and Quick response and it is working as expected.

would anyone please suggest how can we run the same code in SAS Di please.

actually I can see the transpose transformation in sAS DI.iam selecting the columns as per below code in transpose transformation.

but iam not getting required ouput.can you kindly help please

PROC TRANSPOSE DATA=WORK.SORTTempTableSorted
OUT=TRNSTRANSPOSED
PREFIX=VALUE;
BY NAME FILE1 Status;
VAR VALUE;
RUN;

and in mapping taken the columns VALUE NAME FILE1 Status.

iam not getting the transposed output in SAS DI when i open the workoutput .please help

Re: Proc Transpose

JJP1 — Mon, 05 Aug 2019 15:39:13 GMT

Thanks.I got it.i need to set "Update the metadata for the target tables option is set to Yes" which was missing in proc transpose node.