Dear programmers,
I need to transpose my columns in my dataset in a way that some of them will be inserted from two columns into one column.
Let me explain my problem:
I have different wells. At some areas they are perforated to be able to inject water. The perforation starts at a special depth (TOP_DEPTH) and ends longer down (BOTTOM_DEPTH). I know also the UTM coordinate where the perforations begin (TOP_UTM_X & TOP_UTM_Y) and where they end (BOTTOM_UTM_X & BOTTOM_UTM_Y).
What I have is the following:
WELL_NAME | TOP_DEPTH | BOTTOM_DEPTH | TOP_UTM_X | BOTTOM_UTM_X | TOP_UTM_Y | BOTTOM_UTM_Y | PERF_GROUP_NR | PERFORATION_DATE |
A | 1 | 2 | 3 | 5 | 4 | 6 | 1111 | date_1 |
A | 7 | 8 | 9 | 11 | 10 | 12 | 1112 | date_2 |
A | 13 | 14 | 15 | 17 | 16 | 18 | 1113 | date_3 |
A | 19 | 20 | 21 | 23 | 22 | 24 | 1114 | date_4 |
A | 25 | 26 | 27 | 29 | 28 | 30 | 1115 | date_5 |
A | 31 | 32 | 33 | 35 | 34 | 36 | 1116 | date_6 |
A | 37 | 38 | 39 | 41 | 40 | 42 | 1117 | date_7 |
A | 43 | 44 | 45 | 47 | 46 | 48 | 1118 | date_8 |
B | 49 | 50 | 51 | 53 | 52 | 54 | 1119 | date_9 |
B | 55 | 56 | 57 | 59 | 58 | 60 | 1120 | date_10 |
B | 61 | 62 | 63 | 65 | 64 | 66 | 1121 | date_11 |
B | 67 | 68 | 69 | 71 | 70 | 72 | 1122 | date_12 |
B | 73 | 74 | 75 | 77 | 76 | 78 | 1123 | date_13 |
B | 79 | 80 | 81 | 83 | 82 | 84 | 1124 | date_14 |
B | 85 | 86 | 87 | 89 | 88 | 90 | 1125 | date_15 |
B | 91 | 92 | 93 | 95 | 94 | 96 | 1126 | date_16 |
C | 97 | 98 | 99 | 101 | 100 | 102 | 1127 | date_17 |
C | 103 | 104 | 105 | 107 | 106 | 108 | 1128 | date_18 |
C | 109 | 110 | 111 | 113 | 112 | 114 | 1129 | date_19 |
C | 115 | 116 | 117 | 119 | 118 | 120 | 1130 | date_20 |
C | 121 | 122 | 123 | 125 | 124 | 126 | 1131 | date_21 |
C | 127 | 128 | 129 | 131 | 130 | 132 | 1132 | date_22 |
I want to transpose
TOP_DEPTH & BOTTOM_DEPTH into DEPTH
TOP_UTM_X & BOTTOM_UTM_X into UTM_X
TOP_UTM_Y & BOTTOM_UTM_Y into UTM_Y
PERF_GROUP_NR & PERFORATION_DATE will be duplicated for each WELL_NAME during the transpose
I addition I need to have a new column (PERF_LOCATION) to show top and bottom of each perforation group.
What I need to have is this:
WELLBORE_NAME | DEPTH | UTM_X | UTM_Y | PERF_GROUP_NR | PERFORATION_DATE | PERF_LOCATION |
A | 1 | 3 | 4 | 1111 | date_1 | perf_top |
A | 2 | 5 | 6 | 1111 | date_1 | perf_bottom |
A | 7 | 9 | 10 | 1112 | date_2 | perf_top |
A | 8 | 11 | 12 | 1112 | date_2 | perf_bottom |
A | 13 | 15 | 16 | 1113 | date_3 | perf_top |
A | 14 | 17 | 18 | 1113 | date_3 | perf_bottom |
A | 19 | 21 | 22 | 1114 | date_4 | perf_top |
A | 20 | 23 | 24 | 1114 | date_4 | perf_bottom |
A | 25 | 27 | 28 | 1115 | date_5 | perf_top |
A | 26 | 29 | 30 | 1115 | date_5 | perf_bottom |
A | 31 | 33 | 34 | 1116 | date_6 | perf_top |
A | 32 | 35 | 36 | 1116 | date_6 | perf_bottom |
A | 37 | 39 | 40 | 1117 | date_7 | perf_top |
A | 38 | 41 | 42 | 1117 | date_7 | perf_bottom |
A | 43 | 45 | 46 | 1118 | date_8 | perf_top |
A | 44 | 47 | 48 | 1118 | date_8 | perf_bottom |
B | 49 | 51 | 52 | 1119 | date_9 | perf_top |
B | 50 | 53 | 54 | 1119 | date_9 | perf_bottom |
B | 55 | 57 | 58 | 1120 | date_10 | perf_top |
B | 56 | 59 | 60 | 1120 | date_10 | perf_bottom |
B | 61 | 63 | 64 | 1121 | date_11 | perf_top |
B | 62 | 65 | 66 | 1121 | date_11 | perf_bottom |
B | 67 | 69 | 70 | 1122 | date_12 | perf_top |
B | 68 | 71 | 72 | 1122 | date_12 | perf_bottom |
B | 73 | 75 | 76 | 1123 | date_13 | perf_top |
B | 74 | 77 | 78 | 1123 | date_13 | perf_bottom |
B | 79 | 81 | 82 | 1124 | date_14 | perf_top |
B | 80 | 83 | 84 | 1124 | date_14 | perf_bottom |
B | 85 | 87 | 88 | 1125 | date_15 | perf_top |
B | 86 | 89 | 90 | 1125 | date_15 | perf_bottom |
B | 91 | 93 | 94 | 1126 | date_16 | perf_top |
B | 92 | 95 | 96 | 1126 | date_16 | perf_bottom |
C | 97 | 99 | 100 | 1127 | date_17 | perf_top |
C | 98 | 101 | 102 | 1127 | date_17 | perf_bottom |
C | 103 | 105 | 106 | 1128 | date_18 | perf_top |
C | 104 | 107 | 108 | 1128 | date_18 | perf_bottom |
C | 109 | 111 | 112 | 1129 | date_19 | perf_top |
C | 110 | 113 | 114 | 1129 | date_19 | perf_bottom |
C | 115 | 117 | 118 | 1130 | date_20 | perf_top |
C | 116 | 119 | 120 | 1130 | date_20 | perf_bottom |
C | 121 | 123 | 124 | 1131 | date_21 | perf_top |
C | 122 | 125 | 126 | 1131 | date_21 | perf_bottom |
C | 127 | 129 | 130 | 1132 | date_22 | perf_top |
C | 128 | 131 | 132 | 1132 | date_22 | perf_bottom |
It's a beautiful question!
Can you please help me with this?
Thank you very much in advance!
Best regards
Farshid Owrang
First, I have a request. It would help us immensely from now on if you post your data according to these instructions:
In other words, give us data in a form we can work with.
To answer your question, something like this ought to work:
data want;
set have;
depth=top_depth;
utm_x=top_utm_x;
utm_y=top_utm_y;
/* You type the rest */
output;
depth=bottom_depth;
utm_x=bottom_utm_x;
utm_y=bottom_utm_y;
/* You type the rest */
output;
keep well_name depth utm_x utm_y ... ;
run;
First, I have a request. It would help us immensely from now on if you post your data according to these instructions:
In other words, give us data in a form we can work with.
To answer your question, something like this ought to work:
data want;
set have;
depth=top_depth;
utm_x=top_utm_x;
utm_y=top_utm_y;
/* You type the rest */
output;
depth=bottom_depth;
utm_x=bottom_utm_x;
utm_y=bottom_utm_y;
/* You type the rest */
output;
keep well_name depth utm_x utm_y ... ;
run;
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.