BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
forsignupar
Calcite | Level 5

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.

1 ACCEPTED SOLUTION

Accepted Solutions
hbi
Quartz | Level 8 hbi
Quartz | Level 8

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

6 REPLIES 6
hbi
Quartz | Level 8 hbi
Quartz | Level 8

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;

 

forsignupar
Calcite | Level 5

thankyou so much

PGStats
Opal | Level 21

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
LinusH
Tourmaline | Level 20

Why?

Data never sleeps
Steelers_In_DC
Barite | Level 11

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;

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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