Using proc report

[CathyVI]

@PaigeMiller 

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
      

[PaigeMiller]

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.

[CathyVI]

@PaigeMiller Sorry I did not understand what other changes you mean. I read it but I did not understand. Please can you elaborate.

[PaigeMiller]

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. 

[CathyVI]

@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;

[Ksharp]

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;