topic Re: Restructure dataset in SAS Programming

Restructure dataset

forsignupar — Thu, 03 Dec 2015 00:14:56 GMT

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

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.

Re: Restructure dataset

hbi — Thu, 03 Dec 2015 01:14:44 GMT

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;

Re: Restructure dataset

Reeza — Thu, 03 Dec 2015 02:42:04 GMT

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

Re: Restructure dataset

PGStats — Thu, 03 Dec 2015 02:47:54 GMT

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;

Re: Restructure dataset

LinusH — Thu, 03 Dec 2015 08:36:24 GMT

Why?

Re: Restructure dataset

Steelers_In_DC — Thu, 03 Dec 2015 13:42:43 GMT

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;

Re: Restructure dataset

forsignupar — Thu, 03 Dec 2015 16:26:15 GMT

thankyou so much