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;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: