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.
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;
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;
thankyou so much
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;
Why?
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;
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.