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;
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
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.
Ready to level-up your skills? Choose your own adventure.