topic Re: Sum rows and column in Proc SQL in New SAS User

Sum rows and column in Proc SQL

torvyle — Tue, 18 Dec 2018 01:44:13 GMT

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.

Re: Sum rows and column in Proc SQL

Reeza — Tue, 18 Dec 2018 01:56:19 GMT

Why the requirement to do it with SQL?

Re: Sum rows and column in Proc SQL

novinosrin — Tue, 18 Dec 2018 02:03:00 GMT


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;

Re: Sum rows and column in Proc SQL

novinosrin — Tue, 18 Dec 2018 02:04:36 GMT

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;

Re: Sum rows and column in Proc SQL

learsaas — Tue, 18 Dec 2018 02:06:03 GMT

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;

Re: Sum rows and column in Proc SQL

Reeza — Tue, 18 Dec 2018 02:11:49 GMT

@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.

Re: Sum rows and column in Proc SQL

novinosrin — Tue, 18 Dec 2018 02:14:29 GMT

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

Re: Sum rows and column in Proc SQL

andreas_lds — Tue, 18 Dec 2018 07:40:28 GMT

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;

Re: Sum rows and column in Proc SQL

learsaas — Tue, 18 Dec 2018 07:44:59 GMT

@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;

Re: Sum rows and column in Proc SQL

torvyle — Tue, 18 Dec 2018 14:14:44 GMT

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