changing columns into rows

[dan999]

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

[Maheshvaran]

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

[dan999]

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

[Linlin]

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

[dan999]

I'll give this a try. Thanks.

[Linlin · Accepted solution]

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

[rtritz]

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

[dan999]

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

[Linlin]

Hi Rich,

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

Thanks - Linlin

[rtritz]

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

[Linlin]

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

[rtritz]

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

[Linlin]

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

[rtritz]

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

[art297]

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.