BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
dan999
Fluorite | Level 6

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

1 ACCEPTED SOLUTION

Accepted Solutions
Linlin
Lapis Lazuli | Level 10

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

24 REPLIES 24
Maheshvaran
Calcite | Level 5

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
Fluorite | Level 6

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

Linlin
Lapis Lazuli | Level 10

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
Fluorite | Level 6

I'll give this a try. Thanks.

Linlin
Lapis Lazuli | Level 10

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
Calcite | Level 5

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
Fluorite | Level 6

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

Linlin
Lapis Lazuli | Level 10

Hi Rich,

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

Thanks - Linlin

rtritz
Calcite | Level 5

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
Lapis Lazuli | Level 10

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
Calcite | Level 5

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
Lapis Lazuli | Level 10

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
Calcite | Level 5

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

art297
Opal | Level 21

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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