Finding row totals and percentages using SQL

ak2011 — Thu, 09 Jul 2020 06:36:11 GMT

Hello,

I would appreciate if someone can help me with the SAS code to solve this problem using SQL:

For each id, I am summing up row values ie. a1+a2+a3 to obtain a new variable a_total

Next I calculate the row percent to the nearest whole number separately for a1(a1_pct), a2(a2_pct) and a3(a3_pct).

My SAS code and log are as follows and SAS results are attached.

I don't know the code to find the percentages within the SQL procedure so I had to output the results and create a new data step to find the percentages; though the results are right, the process is quite long .

Kindly help me to find the percentages within SQL.

Thanks in advance.

ak.


data envt;
input id$ 1-4 a1 5-6 a2 7-8 a3 10-12;
datalines;
a11	4 2	 62
a12	7 3	 9
a13	8 11 5
a14 2 6  9
a15 3 3  4
;

proc sql;
create table try as
select id, a1, a2, a3,
a1+a2+a3 as a_total 

from envt;
quit;
 
proc print data=try; run;
 
 data try2; set try; 
 a1_pct=(a1/a_total) *100;
 a2_pct=(a2/a_total)*100;
 a3_pct=(a3/a_total)*100;
 run;
proc print data=try2;
run;

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

73 data envt;

74 input id$ 1-4 a1 5-6 a2 7-8 a3 10-12;

75 datalines;

NOTE: The data set WORK.ENVT has 5 observations and 4 variables.

NOTE: DATA statement used (Total process time):

real time 0.01 seconds

cpu time 0.02 seconds

81 ;

83 proc sql;

84 create table try as

85 select id, a1, a2, a3,

86 a1+a2+a3 as a_total

88 from envt;

NOTE: Table WORK.TRY created, with 5 rows and 5 columns.

89 quit;

NOTE: PROCEDURE SQL used (Total process time):

real time 0.01 seconds

cpu time 0.02 seconds

91 proc print data=try; run;

NOTE: There were 5 observations read from the data set WORK.TRY.

NOTE: PROCEDURE PRINT used (Total process time):

real time 0.21 seconds

cpu time 0.22 seconds

93 data try2; set try;

94 a1_pct=(a1/a_total) *100;

95 a2_pct=(a2/a_total)*100;

96 a3_pct=(a3/a_total)*100;

97 run;

NOTE: There were 5 observations read from the data set WORK.TRY.

NOTE: The data set WORK.TRY2 has 5 observations and 8 variables.

NOTE: DATA statement used (Total process time):

real time 0.01 seconds

cpu time 0.02 seconds

98 proc print data=try2;

99 run;

NOTE: There were 5 observations read from the data set WORK.TRY2.

NOTE: PROCEDURE PRINT used (Total process time):

real time 0.11 seconds

cpu time 0.11 seconds

100

101 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

113

Re: Finding row totals and percentages using SQL

Kurt_Bremser — Thu, 09 Jul 2020 06:50:14 GMT

Use the CALCULATED keyword:

data envt;
input id$ 1-4 a1 5-6 a2 7-8 a3 10-12;
datalines;
a11 4 2  62
a12 7 3  9
a13 8 11 5
a14 2 6  9
a15 3 3  4
;

proc sql;
create table try as
  select
    id, a1, a2, a3,
    sum(a1,a2,a3) as a_total,
    a1/calculated a_total as a1_pct format=percent8.2,
    a2/calculated a_total as a2_pct format=percent8.2,
    a3/calculated a_total as a3_pct format=percent8.2
  from envt
;
quit;

Re: Finding row totals and percentages using SQL

ak2011 — Thu, 09 Jul 2020 07:10:32 GMT

Thanks very much! The code works!

Re: Finding row totals and percentages using SQL

Reeza — Thu, 09 Jul 2020 15:51:37 GMT

You know there are PROCS that will do this automatically for you?

topic Re: Finding row totals and percentages using SQL in SAS Programming

Finding row totals and percentages using SQL

Re: Finding row totals and percentages using SQL

Re: Finding row totals and percentages using SQL

Re: Finding row totals and percentages using SQL