Restructure dataset

[forsignupar]

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.

[hbi · Accepted solution]

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;

[Reeza]

Here's two references:
Via an Array
http://www.ats.ucla.edu/stat/sas/modules/longtowide_data.htm

Via proc transpose
http://www.ats.ucla.edu/stat/sas/modules/ltow_transpose.htm

[forsignupar]

thankyou so much

[PGStats]

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;

[LinusH]

Why?

[Steelers_In_DC]

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;