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;
Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!
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.
Ready to level-up your skills? Choose your own adventure.