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;
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;
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;
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
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.