This SAS© script demonstrates how to convert a 'wide' data structure to a 'long' one (or vice versa, depending on the perspective) using the TRANSPOSE procedure. It first generates internal data, performs a transposition with column name management via a prefix, then uses a DATA STEP to apply meaningful business variable names.
Data Analysis
Type : CREATION_INTERNE
The data is generated directly within the script via the DATALINES statement in the DATA Step creating the MYSASLIB.SUBJECTS table.
1 Code Block
DATA STEP Data
Explanation :
Creation of an example 'SUBJECTS' table in the 'MYSASLIB' library containing 4 character columns, populated with static data (DATALINES).
Creation of an example 'SUBJECTS' table in the 'MYSASLIB' library containing 4 character columns, populated with static data (DATALINES).
Copied!
| 1 | DATA MYSASLIB.SUBJECTS; |
| 2 | |
| 3 | INPUT SUB1 $ SUB2 $ SUB3 $ SUB4 $; |
| 4 | DATALINES; |
| 5 | 12 21 13 14 |
| 6 | 13 21 12 14 |
| 7 | 15 31 23 23 |
| 8 | 15 33 21 32 |
| 9 | M F F M |
| 10 | ; |
2 Code Block
PROC TRANSPOSE Data
Explanation :
Simple transposition of variables SUB1 to SUB4. The new columns generated will take default names (COL1, COL2, etc.).
Simple transposition of variables SUB1 to SUB4. The new columns generated will take default names (COL1, COL2, etc.).
Copied!
| 1 | |
| 2 | PROC TRANSPOSE |
| 3 | DATA=MYSASLIB.SUBJECTS OUT=MYSASLIB.TRANSPOSED; |
| 4 | VAR SUB1 SUB2 SUB3 SUB4; |
| 5 | RUN; |
| 6 |
3 Code Block
PROC TRANSPOSE Data
Explanation :
Second transposition of the same variables, but using the PREFIX=INFO option to name the resulting columns (INFO1, INFO2, etc.).
Second transposition of the same variables, but using the PREFIX=INFO option to name the resulting columns (INFO1, INFO2, etc.).
Copied!
| 1 | |
| 2 | PROC TRANSPOSE |
| 3 | DATA=MYSASLIB.SUBJECTS OUT=MYSASLIB.TRANSPOSED2 PREFIX=INFO; |
| 4 | VAR SUB1 SUB2 SUB3 SUB4; |
| 5 | RUN; |
| 6 |
4 Code Block
PROC PRINT
Explanation :
Displaying the result of the second transposition for verification.
Displaying the result of the second transposition for verification.
Copied!
| 1 | PROC PRINT DATA=MYSASLIB.TRANSPOSED2; |
| 2 | RUN; |
5 Code Block
DATA STEP Data
Explanation :
Creation of the final 'NEW_SUBJ' table from the transposed table. Use of the RENAME statement to assign business names to automatically generated columns.
Creation of the final 'NEW_SUBJ' table from the transposed table. Use of the RENAME statement to assign business names to automatically generated columns.
Copied!
| 1 | DATA MYSASLIB.NEW_SUBJ; |
| 2 | SET MYSASLIB.TRANSPOSED2; |
| 3 | RENAME INFO1=T1 INFO2=T2 |
| 4 | INFO3=T3 INFO4=T4 |
| 5 | INFO5=GENDER _NAME_=SUBJECT; |
| 6 | RUN; |
6 Code Block
PROC PRINT
Explanation :
Final report displaying the restructured and renamed data, enhanced with a title and a footnote.
Final report displaying the restructured and renamed data, enhanced with a title and a footnote.
Copied!
| 1 | PROC PRINT DATA=MYSASLIB.NEW_SUBJ; |
| 2 | RUN; |
| 3 | TITLE 'transpose function'; |
| 4 | FOOTNOTE 'result of TP'; |
This material is provided "as is" by We Are Cas. There are no warranties, expressed or implied, as to merchantability or fitness for a particular purpose regarding the materials or code contained herein. We Are Cas is not responsible for errors in this material as it now exists or will exist, nor does We Are Cas provide technical support for it.
Copyright Info : (C) 2016 Elliott, Alan C. and Woodward, Wayne A.
