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;
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.