What is wrong with my code here
NOTE: Writing HTML5(EGHTML) Body file: EGHTML
27
28 proc sql;
29 create table test4 as
30 select a.*, b.Active as Active_total,
31 (Active/Active_total)*100 as Active_pct
32
33 from test3 a
34 left join test3 b
35 (where=( serv_ord='_6TOTAL'))
_
73
76
ERROR 73-322: Expecting an ON.
ERROR 76-322: Syntax error, statement will be ignored.
36 on a.table=b.table;
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
37
38 quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
cpu time 0.01 seconds
This was explained in my previous post at https://communities.sas.com/t5/SAS-Programming/Using-proc-report/m-p/924148#M363762
You didn't make the changes (plural) that I suggested. You only made one change.
@PaigeMiller Sorry I did not understand what other changes you mean. I read it but I did not understand. Please can you elaborate.
You can't use
(where=( b.serv_ord='TOTAL'))
after the letter B. This is a DATA set option, it must go after the data set name.
@PaigeMiller Thank you.
I have clean up the data and this is the output I have now. I am getting close to my goal but I need help in calculating percentage for each of the count. For example: I want the n(%) for Boy in PHA(table) who are Active, N(%) for Boy in 2796 who are active and so on. I want for the reserve group too. Just like my desired table I show you earlier. I think I need numerator & denominator. For Boy in PHA who are active, my numerator is 41043/706476 (total PHA). I want to do this for both active and reserve group, so I can have a similar outcome that i can output with proc report. Thanks for you help @PaigeMiller
I test this code for the percent but it's not given me desire output
proc sql;
create table want as
select serv,count(active) as frequency,
calculated frequency/(select count(*) from test3) as pct format=percent.
from test3
group by serv, table;
quit;
data test;
input serv $ table $ comp $ numb ;
datalines;
Boy 2796 Active 41043
Boy 2796 Reserve 7024
Boy 2796 missing 72
Boy PHA Active 274220
Boy PHA Reserve 95291
Girl 2796 Active 3200
Girl 2796 Reserve 1231
Girl 2796 missing 5
Girl PHA Active 121634
Girl PHA Reserve 36043
Dad 2796 Active 9915
Dad 2796 Reserve 310
TOTAL 2796 Active 77283
TOTAL PHA Active 77283
TOTAL 2796 Reserve 77283
TOTAL PHA Reserve 195988
TOTAL 2796 missing 195988
; run;
proc sql;
create table report as
select 'Comp' as header,*,
numb/(select numb from test where serv='TOTAL' and table=a.table and comp=a.comp) as per format=percent8.2
from test as a;
quit;
proc report data=report nowd;
column serv table header,comp,(numb per);
define serv/group order=data;
define table/group descending;
define header/across '';
define comp/across '';
define numb/analysis 'N';
define per/analysis '%';
run;
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!
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.