BookmarkSubscribeRSS Feed
scottsanchez26
Calcite | Level 5

Hello,

I'm trying to combine 3 columns with four observations into a single column with twelve observations.

If I have data that looks like:

 

A B C

1  5 9

2 6 10

3 7 11

4 8 12

 

How would I get it to look like:

D

1

2

3

4

5

6

7

8

9

10

11

12

 

Thanks!

8 REPLIES 8
Reeza
Super User

Does order matter?

 

Data want;
Set have(rename=a=d)
      Have(rename=b=d)
      Have(rename=c=d);

Keep d;
Run;

If order doesn't matter use proc transpose. 

scottsanchez26
Calcite | Level 5

Now that I look at it more closely it should look like:

A 1

A 2

A 3

A 4

B 5

B 6

.

.

.C 12

 

Thanks!

Reeza
Super User

Proc transpose and then sort. 

 

Reeza
Super User
Proc transpose data=have out=want;
Run;

Proc sort data=want;
By _name_;
Run;
scottsanchez26
Calcite | Level 5

I'm not getting the output that I'm looking for. I'm getting 3 observations with multiple columns. I would like there to be two columns with 12 observations. I'm not sure what I'm doing wrong here.

 

data have;
infile 'have.txt';
input A B C;


Proc transpose data=have out=want;
Run;

Proc sort data=want;
By _name_;
Run;

Reeza
Super User

You're correct, proc transpose seems to want an ID, I added a row number to implement this. You could also just transpose using a data step as well if you wanted.

 

data have;
input A B C;
cards;
1 5 9
2 6 10
3 7 11
4 8 12
;
run;

data want; set have; array vars(3) a b c; do i=1 to dim(vars); value=vars(i); name = vname(vars(i)); output; end; keep value name; run; proc sort data=want; by name; run;

Or the Proc Transpose method:

 

 

data have;
input A B C;
cards;
1  5 9
2 6 10
3 7 11
4 8 12
;
run;

data have;
set have;
id=_n_;
run;

proc transpose data=have out=want;
by id;
run;

proc sort data=want;
by _name_;
run;

proc print data=want;
run;

 

 

scottsanchez26
Calcite | Level 5

I used the proc transpose method and it worked great!

 

Thank you!

PGStats
Opal | Level 21

@Reeza's initial idea could have been extended in the following way:

 

Data want;
length var $8;
Set have(rename=a=d in=ina)
    Have(rename=b=d in=inb)
    Have(rename=c=d in=inc);
var = choosec(ina+2*inb+3*inc,"A", "B", "C");
Keep var d;
Run;
PG

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!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 8 replies
  • 1202 views
  • 2 likes
  • 3 in conversation