topic changing columns into rows in SAS Procedures

changing columns into rows

dan999 — Fri, 09 Mar 2012 20:03:38 GMT

I have some output that looks like this:

program col1 col2 col3

10 3 5 1

15 1 2 7

24 6 8 5

21 2 8 1

35 9 4 3

33 3 1 1

I want to group the programs and sum the corresponding columns for the group (i.e. I want to call programs 10 and 15 prgA and 24 and 21 prgB and 35 and 33 prg C.

Then I want the output to look like this:

prgA prgB prgC

col1 4 8 12

col2 7 16 5

col3 8 6 4

Can I do this in proc sql?

Thanks

changing columns into rows

Maheshvaran — Fri, 09 Mar 2012 20:24:08 GMT

Hi Dan,

I dont want to give any definate answers, but this is much more easily dealt with by using PROC TRANSPOSE, are you aware of it?

Kind Regards,

Maheshvaran

changing columns into rows

Linlin — Fri, 09 Mar 2012 20:25:35 GMT

This is not a proc sql solution.

data have;

input program: col1 col2 col3;

cards;

10 3 5 1

15 1 2 7

24 6 8 5

21 2 8 1

35 9 4 3

33 3 1 1

;

data temp;

set have;

length group $ 5;

if program in (10,15) then group='proA';

else if program in (21,24) then group='proB';

else group='proC';

proc summary data=temp nway;

class group;

var col:;

output out=temp1(drop=_:) sum=;

run;

proc transpose data=temp1 out=want;

id group;

run;

proc print;run;

Obs _NAME_ proA proB proC

1 col1 4 8 12

2 col2 7 16 5

3 col3 8 6 4

Re: changing columns into rows

Linlin — Fri, 09 Mar 2012 20:51:54 GMT

50% proc sql solution:

data have;

input program: col1 col2 col3;

cards;

10 3 5 1

15 1 2 7

24 6 8 5

21 2 8 1

35 9 4 3

33 3 1 1

;

proc sql;

create table temp as select

case when program in (10,15) then 'proA'

when program in (21,24) then 'proB'

else 'proC'

end as gp,

sum(col1) as col1,

sum(col2) as col2,

sum(col3) as col3

from have

group by gp;

quit;

proc transpose data=temp out=want;

id gp;

run;

proc print data=want;run;

Message was edited by: Linlin

changing columns into rows

dan999 — Fri, 09 Mar 2012 20:54:34 GMT

I'm not but I'll look it up. Thanks.

changing columns into rows

dan999 — Fri, 09 Mar 2012 20:54:54 GMT

I'll give this a try. Thanks.

changing columns into rows

dan999 — Fri, 09 Mar 2012 22:07:30 GMT

This worked. Thanks a lot.

changing columns into rows

rtritz — Fri, 09 Mar 2012 22:11:33 GMT

Since variety is the spice of life, here is another solution.

Not proc sql but uses a select statement.

data have;

input program: col1 col2 col3;

cards;

10 3 5 1

15 1 2 7

24 6 8 5

21 2 8 1

35 9 4 3

33 3 1 1

;

proc transpose data = have out=thave;

var col1-col3;

run;

data test (drop=col:);

set thave;

select(_name_);

when ('COL1') do;

PrgA = sum(col1,col2);

PrgB = sum(col3,col4);

PrgC = sum(col5,col6);

end;

when ('COL2') do;

PrgA = sum(col1,col2);

PrgB = sum(col3,col4);

PrgC = sum(col5,col6);

end;

when ('COL3') do;

PrgA = sum(col1,col2);

PrgB = sum(col3,col4);

PrgC = sum(col5,col6);

end;

otherwise;

end;

run;

Rich

changing columns into rows

dan999 — Fri, 09 Mar 2012 22:15:19 GMT

Thanks Rich. I'll try it too and put it in the library.

Re: changing columns into rows

MikeZdeb — Fri, 09 Mar 2012 22:26:22 GMT

hi ... another idea ...

data x;

input program col1-col3 @@;

datalines;

10 3 5 1 15 1 2 7 24 6 8 5 21 2 8 1 35 9 4 3 33 3 1 1

;

proc format;

value p

10,15 = 'A'

24,21 = 'B'

35,33 = 'C'

;

run;

proc summary data=x nway;

class program;

var col: ;

output out=stats sum=;

format program p.;

run;

proc transpose data=stats out=want (drop=_name_) prefix=prg;

var col1-col3;

id program;

run;

Obs prgA prgB prgC

1 4 8 12

2 7 16 5

3 8 6 4

Re: changing columns into rows

Linlin — Fri, 09 Mar 2012 23:48:50 GMT

Hi Rich,

I think you may need to change "select(_name_);" to "select(upcase(_name_));".

Thanks - Linlin

changing columns into rows

rtritz — Sat, 10 Mar 2012 00:37:14 GMT

Hi LInlin,

Technically, upcase is not needed because the _name_ values are already uppercase in the thave dataset, so to quote the literal you do not need upcase.

Actually, to make my output look exactly like Dan999 I should have done an assignment statement like _name_ = lowcase(_name_)

somewhere in my code to get col1-col3 in lowercase.

Rich

changing columns into rows

Linlin — Sat, 10 Mar 2012 01:08:16 GMT

Hi Rich,

I use pc sas (9.3). the _name_ values I generated are lowcase in the thave dataset. Below is what I got when I ran your code:

data have;

input program: col1 col2 col3;

cards;

10 3 5 1

15 1 2 7

24 6 8 5

21 2 8 1

35 9 4 3

33 3 1 1

;

proc transpose data = have out=thave;

var col1-col3;

run;

data test (drop=col:);

set thave;

select(_name_);

when ('COL1') do;

PrgA = sum(col1,col2);

PrgB = sum(col3,col4);

PrgC = sum(col5,col6);

end;

when ('COL2') do;

PrgA = sum(col1,col2);

PrgB = sum(col3,col4);

PrgC = sum(col5,col6);

end;

when ('COL3') do;

PrgA = sum(col1,col2);

PrgB = sum(col3,col4);

PrgC = sum(col5,col6);

end;

otherwise;

end;

run;

proc print data=test;run;

Obs _NAME_ PrgA PrgB PrgC

1 col1 . . .

2 col2 . . .

3 col3 . . .

Thanks - Linlin

Re: changing columns into rows

rtritz — Sat, 10 Mar 2012 02:11:23 GMT

Linlin, That is interesting because I am using pc SAS(9.1.3) and the values in the _name_ column are uppercase after transposing with proc transpose. So, a change from 9.1.3 to 9.3. Interesting to see that they even change the little things in these upgrades. It will make for easier typing not to have to press the shift key when quoting the literal if and when I get to work with 9.3. This is the output from SAS 9.1.3 on my computer. You expect code from 9.3 not to work in 9.1.3 but not necessarily the other way around.

_NAME_ PRGA PRGB PRGC

COL1 4 8 12 2

COL2 7 16 5 3

COL3 8 6 4

Interesting,

Rich

Re: changing columns into rows

Linlin — Sat, 10 Mar 2012 04:17:16 GMT

Hi Rich,

Yes, it is interesting. And your code works fine without select statement.

proc transpose data = have out=thave;

var col1-col3;

run;

data test (drop=col:);

set thave;

PrgA = sum(col1,col2);

PrgB = sum(col3,col4);

PrgC = sum(col5,col6);

run;

proc print data=test;run;

Obs _NAME_ PrgA PrgB PrgC

1 col1 4 8 12

2 col2 7 16 5

3 col3 8 6 4

Thanks - Linlin

changing columns into rows

rtritz — Sat, 10 Mar 2012 07:48:42 GMT

Hi Linlin, Not sure now why I fixated on the select statement. Nice catch, that one just slipped by me. Rich

changing columns into rows

art297 — Sat, 10 Mar 2012 13:17:29 GMT

rtritz and Linlin,

Before we all start looking for different behaviors between 9.1 and 9.3, what kind of computers are you each running? Operating system differences might be what is causing the two of you to get different results.

Re: changing columns into rows

Linlin — Sat, 10 Mar 2012 13:56:14 GMT

Good morning Art!

I am using a Lenovo laptop with Windows 7.

Re: changing columns into rows

rtritz — Sat, 10 Mar 2012 20:47:40 GMT

Hi Art and Linlin, I have Sony laptop with Windows Vista.

Afterwards, I was wondering about something like this or a system option that may have a different value.

Rich

Re: changing columns into rows

art297 — Sat, 10 Mar 2012 22:20:47 GMT

Rich,

What do you get if you run:

proc options option= caps;

run;

I ran your code on 9.2 and got the same result as Linlin did.