Sum rows and column in Proc SQL

[torvyle]

Hello,

Say that I have a table like this:

	Measure1	Measure2	Measure3
Id1	4	2	6
Id2	7	3	9
Id3	8	1	5

And I want Proc SQL to output the following table:

	Count	Percentage
Id1	12	26.7
Id2	19	42.2
Id3	14	31.1

Count is a row's sum and percentage is a row's sum*100/column sum of "Count". How can I go about doing this? Thank you in advance.

[Reeza]

Why the requirement to do it with SQL?

[novinosrin · Accepted solution]

data have;
input id $ measure1-measure3;
cards;
Id1	4	2	6
Id2	7	3	9
Id3	8	1	5
;

proc sql;
create table want as
select id,sum(measure1,measure2,measure3) as count, calculated count/sum( calculated count)*100 as pct
from have;
quit;

[novinosrin]

or instead of *100, apply format percent.

data have;
input id $ measure1-measure3;
cards;
Id1	4	2	6
Id2	7	3	9
Id3	8	1	5
;

proc sql;
create table want as
select id,sum(measure1,measure2,measure3) as count, calculated count/sum( calculated count)  as pct format=percent.
from have;
quit;

[learsaas]

proc sql noprint;
	create table result as
		select (case when Measure1=. then 0 else Measure1 end)+
		       (case when Measure2=. then 0 else Measure2 end)+
			   (case when Measure3=. then 0 else Measure3 end) as count,
			   (case when calculated count in (.,0) then . else round(calculated count/sum(calculated count)*100,.1)  end) as Percentage
		from a;
quit;

[Reeza]

---

@learsaas wrote:

proc sql noprint;
	create table result as
		select (case when Measure1=. then 0 else Measure1 end)+
		       (case when Measure2=. then 0 else Measure2 end)+
			   (case when Measure3=. then 0 else Measure3 end) as count,
			   (case when calculated count in (.,0) then . else round(calculated count/sum(calculated count)*100,.1)  end) as Percentage
		from a;
quit;

---

The SUM() function accounts for missing by essentially considering it as 0. 

[novinosrin]

Are you so wide awake at this time? Kudos . I wish I had your energy. 

[learsaas]

---

@Reeza wrote:

---

@learsaas wrote:

proc sql noprint;
	create table result as
		select (case when Measure1=. then 0 else Measure1 end)+
		       (case when Measure2=. then 0 else Measure2 end)+
			   (case when Measure3=. then 0 else Measure3 end) as count,
			   (case when calculated count in (.,0) then . else round(calculated count/sum(calculated count)*100,.1)  end) as Percentage
		from a;
quit;

---

The SUM() function accounts for missing by essentially considering it as 0. 

The SUM() function accounts for missing by essentially considering it as 0. ？？？？？？？？？？？？？？？？？？？？？

data _null_;
	x=sum(.);
	put x=;
run;

---

[andreas_lds]

Are there always exactly three measure-variables?

If not, i would start by transposing the in source dataset to get rid of the not-that-perfect structure:

proc transpose data= work.have out=work.transposed(rename=(_name_=MeasureNo col1=value));
   by id;
   var measure1 measure2 measure3;
run;

Then you can get rid of proc sql  and use a reporting procedure like proc tabluate to generate the required output:

proc tabulate data=work.transposed;
   var value;
   class id / order=unformatted missing;
   table
      id,
      value*(sum colpctsum)
   ;
run;

[torvyle]

Thanks everyone. There are more than 3 "measure" variables so novinosrin's solution seems to be the most efficient.