Help using Base SAS procedures

changing columns into rows

Accepted Solution Solved
Reply
Contributor
Posts: 37
Accepted Solution

changing columns into rows

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


Accepted Solutions
Solution
‎03-09-2012 03:51 PM
Super Contributor
Posts: 1,636

Re: changing columns into rows

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

View solution in original post


All Replies
Occasional Contributor
Posts: 13

changing columns into rows

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

Contributor
Posts: 37

changing columns into rows

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

Super Contributor
Posts: 1,636

changing columns into rows

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=_Smiley Happy 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

Contributor
Posts: 37

changing columns into rows

I'll give this a try. Thanks.

Solution
‎03-09-2012 03:51 PM
Super Contributor
Posts: 1,636

Re: changing columns into rows

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

Contributor
Posts: 23

changing columns into rows

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=colSmiley Happy;  

  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

Contributor
Posts: 37

changing columns into rows

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

Super Contributor
Posts: 1,636

Re: changing columns into rows

Hi Rich,

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

Thanks - Linlin

Contributor
Posts: 23

changing columns into rows

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

Super Contributor
Posts: 1,636

changing columns into rows

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=colSmiley Happy

  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

Contributor
Posts: 23

Re: changing columns into rows

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

Super Contributor
Posts: 1,636

Re: changing columns into rows

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=colSmiley Happy

  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

Contributor
Posts: 23

changing columns into rows

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

PROC Star
Posts: 7,416

changing columns into rows

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.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 24 replies
  • 1467 views
  • 1 like
  • 6 in conversation