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;
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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: