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
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
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
I'm not but I'll look it up. Thanks.
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
I'll give this a try. Thanks.
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
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
Thanks Rich. I'll try it too and put it in the library.
Hi Rich,
I think you may need to change "select(_name_);" to "select(upcase(_name_));".
Thanks - Linlin
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
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
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
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
Hi Linlin, Not sure now why I fixated on the select statement. Nice catch, that one just slipped by me. Rich
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: