Solved
New Contributor
Posts: 3

# Restructure dataset

How can I restructure a dataset like this:

A  B  C

1  4  3

1  6  4

2  7  6

2  3  7

3  2  8

3  1  1

to

A B1 B2 C1 C2

1  4    6   3   4

2  7    3   6   7

3  2    1   8   1

What is the code if i want to do it by ARRAY or without ARRAY?

Thank you.

Accepted Solutions
Solution
‎12-03-2015 11:25 AM
Contributor
Posts: 66

## Re: Restructure dataset

Here are two versions that should give you what you need. Enjoy.

``````DATA have;
LENGTH A B C 8;
INPUT A B C;
DATALINES;
1  4  3
1  6  4
2  7  6
2  3  7
3  2  8
3  1  1
;
RUN;

/* don't run PROC SORT because it will change the order of variables B and C */
/* PROC SORT DATA=have;
BY A B C;
RUN;
*/

/**************************************/
/* version 1 - pure data step version */
/**************************************/
DATA want1(DROP=B C);
SET have;
BY A;
LENGTH B1 B2 C1 C2 8;
IF FIRST.A THEN DO;
B1 = B;
C1 = C;
RETAIN B1 C1;
DELETE;
END;
IF LAST.A THEN DO;
B2 = B;
C2 = C;
END;
RUN;

/********************************************/
/* version 2 - data step + PROC SQL version */
/********************************************/
DATA have;
SET have;
BY A;
IF FIRST.A THEN counter_a = 1;
ELSE            counter_a + 1;
RUN;

PROC SQL;
CREATE TABLE want2 AS
SELECT A
, MAX(CASE WHEN counter_a = 1 THEN B END) AS B1
, MAX(CASE WHEN counter_a = 2 THEN B END) AS B2
, MAX(CASE WHEN counter_a = 1 THEN C END) AS C1
, MAX(CASE WHEN counter_a = 2 THEN C END) AS C2
FROM have
GROUP BY A;
QUIT;``````

All Replies
Solution
‎12-03-2015 11:25 AM
Contributor
Posts: 66

## Re: Restructure dataset

Here are two versions that should give you what you need. Enjoy.

``````DATA have;
LENGTH A B C 8;
INPUT A B C;
DATALINES;
1  4  3
1  6  4
2  7  6
2  3  7
3  2  8
3  1  1
;
RUN;

/* don't run PROC SORT because it will change the order of variables B and C */
/* PROC SORT DATA=have;
BY A B C;
RUN;
*/

/**************************************/
/* version 1 - pure data step version */
/**************************************/
DATA want1(DROP=B C);
SET have;
BY A;
LENGTH B1 B2 C1 C2 8;
IF FIRST.A THEN DO;
B1 = B;
C1 = C;
RETAIN B1 C1;
DELETE;
END;
IF LAST.A THEN DO;
B2 = B;
C2 = C;
END;
RUN;

/********************************************/
/* version 2 - data step + PROC SQL version */
/********************************************/
DATA have;
SET have;
BY A;
IF FIRST.A THEN counter_a = 1;
ELSE            counter_a + 1;
RUN;

PROC SQL;
CREATE TABLE want2 AS
SELECT A
, MAX(CASE WHEN counter_a = 1 THEN B END) AS B1
, MAX(CASE WHEN counter_a = 2 THEN B END) AS B2
, MAX(CASE WHEN counter_a = 1 THEN C END) AS C1
, MAX(CASE WHEN counter_a = 2 THEN C END) AS C2
FROM have
GROUP BY A;
QUIT;``````

Super User
Posts: 23,700

New Contributor
Posts: 3

thankyou so much

Posts: 5,526

## Re: Restructure dataset

Without arrays

``````
proc transpose data=have out=ab(drop=_name_) prefix=B; by A; var B; run;
proc transpose data=have out=ac(drop=_name_) prefix=C; by A; var C; run;

data want;
merge ab ac; by a;
run;``````
PG
Super User
Posts: 5,876

## Re: Restructure dataset

Why?

Data never sleeps
Valued Guide
Posts: 863

## Re: Restructure dataset

Here's a solution:

data have;
input A  B  C;
cards;
1  4  3
1  6  4
2  7  6
2  3  7
3  2  8
3  1  1
;

data want(rename=(b = b2 c = c2));
set have;
by a;
retain b1 c1;
if first.a then do;
b1 = b;
c1 = c;
end;
if last.a then output;
run;

🔒 This topic is solved and locked.