BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
farshidowrang
Quartz | Level 8

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 

1 ACCEPTED SOLUTION

Accepted Solutions
PaigeMiller
Diamond | Level 26

First, I have a request. It would help us immensely from now on if you post your data according to these instructions:

https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...

 

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;
--
Paige Miller

View solution in original post

2 REPLIES 2
PaigeMiller
Diamond | Level 26

First, I have a request. It would help us immensely from now on if you post your data according to these instructions:

https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...

 

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;
--
Paige Miller
farshidowrang
Quartz | Level 8
Thank you very much my friend!
Best regards
Farshid

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

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!

Register Now

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 2 replies
  • 843 views
  • 1 like
  • 2 in conversation