DATA Step, Macro, Functions and more

Restructure dataset

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

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 hbi
Contributor
Posts: 66

Re: Restructure dataset

Posted in reply to forsignupar

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;

 

View solution in original post


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

Re: Restructure dataset

Posted in reply to forsignupar

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: 19,787

Re: Restructure dataset

Posted in reply to forsignupar
New Contributor
Posts: 3

Re: Restructure dataset

thankyou so much

Respected Advisor
Posts: 4,920

Re: Restructure dataset

Posted in reply to forsignupar

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,426

Re: Restructure dataset

Posted in reply to forsignupar

Why?

Data never sleeps
Valued Guide
Posts: 860

Re: Restructure dataset

Posted in reply to forsignupar

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 283 views
  • 0 likes
  • 6 in conversation