Write and run SAS programs in your web browser

Multiple columns into one

Reply
New Contributor
Posts: 4

Multiple columns into one

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!

Grand Advisor
Posts: 16,416

Re: Multiple columns into one

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. 

New Contributor
Posts: 4

Re: Multiple columns into one

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!

Grand Advisor
Posts: 16,416

Re: Multiple columns into one

Proc transpose and then sort. 

 

Grand Advisor
Posts: 16,416

Re: Multiple columns into one

Proc transpose data=have out=want;
Run;

Proc sort data=want;
By _name_;
Run;
New Contributor
Posts: 4

Re: Multiple columns into one

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;

Grand Advisor
Posts: 16,416

Re: Multiple columns into one

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;

 

 

New Contributor
Posts: 4

Re: Multiple columns into one

I used the proc transpose method and it worked great!

 

Thank you!

Respected Advisor
Posts: 4,606

Re: Multiple columns into one

@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
Post a Question
Discussion Stats
  • 8 replies
  • 246 views
  • 2 likes
  • 3 in conversation