Transpose two columns in one column

farshidowrang — Wed, 11 Dec 2019 21:24:18 GMT

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

Re: Transpose two columns in one column

PaigeMiller — Wed, 11 Dec 2019 22:44:57 GMT

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-data-AKA-generate/ta-p/258712

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;

Re: Transpose two columns in one column

farshidowrang — Thu, 12 Dec 2019 08:20:23 GMT

Thank you very much my friend!
Best regards
Farshid

topic Re: Transpose two columns in one column in SAS Programming

Transpose two columns in one column

Re: Transpose two columns in one column

Re: Transpose two columns in one column