topic Re: Multiple columns into one in SAS Studio

Multiple columns into one

scottsanchez26 — Sun, 19 Jun 2016 20:52:22 GMT

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:

Thanks!

Re: Multiple columns into one

Reeza — Sun, 19 Jun 2016 21:07:15 GMT

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.

Re: Multiple columns into one

scottsanchez26 — Sun, 19 Jun 2016 21:11:54 GMT

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!

Re: Multiple columns into one

Reeza — Sun, 19 Jun 2016 21:19:22 GMT

Proc transpose and then sort.

Re: Multiple columns into one

Reeza — Sun, 19 Jun 2016 21:23:12 GMT

Proc transpose data=have out=want;
Run;

Proc sort data=want;
By _name_;
Run;

Re: Multiple columns into one

scottsanchez26 — Sun, 19 Jun 2016 22:00:18 GMT

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;

Re: Multiple columns into one

Reeza — Sun, 19 Jun 2016 22:13:00 GMT

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;

Re: Multiple columns into one

scottsanchez26 — Mon, 20 Jun 2016 02:53:28 GMT

I used the proc transpose method and it worked great!

Thank you!

Re: Multiple columns into one

PGStats — Mon, 20 Jun 2016 04:35:37 GMT

@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;