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.
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;
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;
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;
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;
@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.
Are you so wide awake at this time? Kudos . I wish I had your energy.
@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;
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;
Thanks everyone. There are more than 3 "measure" variables so novinosrin's solution seems to be the most efficient.
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 25. Read more here about why you should contribute and what is in it for you!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.