topic Re: Using proc report in SAS Programming

Using proc report

CathyVI — Fri, 12 Apr 2024 12:40:50 GMT

Hello,

I need help. I am just learning proc report. I have created a demo of my data and desired output. Numb is the count of boys in table 2796 who are active. I would to use PROC REPORT to generate this output. Thanks

data test;
input serv$ 1-5	table$ 6-10	comp$ 11-18	numb 19-28;
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
; run;

desire output

		Comp
Serv	Table	Active	Reserve	Missing
		N	%	N	%	N	%
Boy	PHA
	2796
Girl
	PHA
	2796
Dad
	PHA
	2796
TOTAL
	PHA
	2796

Re: Using proc report

PaigeMiller — Fri, 12 Apr 2024 12:55:45 GMT

Regarding the columns for %

To compute a percent, we need a numerator and a denominator. Please state clearly the numerator and denominator of this percent.

Also, your code does not work as shown. Please test your code before you post it. Testing is a good thing, and should be considered mandatory. The code shown needs to be fixed. By providing properly tested code, you won't be wasting our time and you will get faster answers.

Re: Using proc report

CathyVI — Fri, 12 Apr 2024 13:08:59 GMT

@PaigeMiller Thank you. I created the dummy code in sas, so I tested it. I just copied directly maybe there is something am not doing right, i will appreciate your guidance.

My numerator is each active, server and missing, while denominator is the total.

e.g (active/active_total)*100 as active_pct

I am trying to find the count of each group which i was able to do with proc freq but i dont know how to use proc report.

Re: Using proc report

PaigeMiller — Fri, 12 Apr 2024 13:14:38 GMT

Your code produces a lot of missing values, I assume you don't want that. So it needs to be fixed.

My numerator is each active, server and missing, while denominator is the total.

e.g (active/active_total)*100 as active_pct

So for Boy PHA Active, the percent is Boy PHA Active N divided by Total PHA Active N?

Re: Using proc report

CathyVI — Fri, 12 Apr 2024 13:20:31 GMT

@PaigeMiller Yes that is correct.

Yes I will take care of the missing after I see how it impact the final output. Thanks

Re: Using proc report

PaigeMiller — Fri, 12 Apr 2024 13:22:39 GMT

@CathyVI wrote:

@PaigeMiller Yes that is correct.

Okay, this doesn't seem to me to be something that can be done (easily) in PROC REPORT. What I typically do is calculate the percents in a DATA step or SQL before I run PROC REPORT. Once those calculations are done, then getting PROC REPORT to display the table you want is much easier.

Re: Using proc report

CathyVI — Fri, 12 Apr 2024 13:25:04 GMT

@PaigeMiller I think the table messed up when I copied into SAS. This is how table should look like.

Re: Using proc report

Cynthia_sas — Fri, 12 Apr 2024 13:33:44 GMT

Hi:

When I tried to read your file, the issue I ran into was the tabs generated missing values with your type of input. This worked better for me.

data test;
length serv $5 table $5 comp $8;
infile datalines dlm=',' dsd;
input serv $ 	table $  comp $  	numb  ;
dataord = _n_;
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
; 
run;

I added a "DATAORD" variable to capture the original order of the variables, thinking it might help, but your input data is not in the order shown in your desired results, so I'm not sure it is useful.

However, without some direction or a helper variable, to assure the desired ordering, PROC REPORT would not be able to do the type of ordering you show. What PROC REPORT code have you tried?

Cynthia

Re: Using proc report

PaigeMiller — Fri, 12 Apr 2024 13:34:48 GMT

@CathyVI wrote:

@PaigeMiller I think the table messed up when I copied into SAS. This is how table should look like.

That's good to know, but I still don't think PROC REPORT will do the calculations (easily). I still think you are better off doing the calculations of percent in a DATA step or SQL, and then use PROC REPORT to display the table.

Re: Using proc report

CathyVI — Fri, 12 Apr 2024 13:45:53 GMT

@Cynthia_sas @PaigeMiller

I tried to transpose then calculated % i could have the desired table format- just as @PaigeMiller said. But I encountered an issue.

proc sort data= test ;
by serv table comp ; run;
proc transpose data= test out=trans (drop=_name_);
by serv table ;
id comp ; 
var numb;
run;

proc sql;
create table test2 as
select a.*, b.Active as Active_total, 
	(Active/Active_total)*100 as Active_pct

from test a
	left join trans1 (where=( service='TOTAL')) b
		on a.table=b.table;

quit;

First issue: With the transpose, how do I get a single column for Active, reserve and missing. i dont want them repeated.

Second issue: How do I calculate the % with my suggested code. I encounter an error

ERROR: Ambiguous reference, column Active is in more than one table.

Re: Using proc report

PaigeMiller — Fri, 12 Apr 2024 14:21:43 GMT

How can we know what you did wrong if you don't show us the code? How can we know what you did wrong if you don't show us the fixed code to read in the data? By the way, you seem to be calculating percents in your PROC SQL, but then your table doesn't show percents.

Also, If you get errors from running a PROC, you need to show us the log for that PROC — and we need to see every line in the log of that PROC, including code, errors, warnings and notes.

Re: Using proc report

CathyVI — Fri, 12 Apr 2024 14:23:59 GMT

@PaigeMiller @Cynthia_sas I posted the code here

proc sort data= test ;
by serv table comp ; run;
proc transpose data= test out=trans (drop=_name_);
by serv table ;
id comp ; 
var numb;
run;

proc sql;
create table test2 as
select a.*, b.Active as Active_total, 
	(Active/Active_total)*100 as Active_pct

from test a
	left join trans1 (where=( service='TOTAL')) b
		on a.table=b.table;

quit;

27 
28 proc sql;
29 create table test2 as
30 select a.*, b.Active as Active_total,
31 (Active/Active_total)*100 as Active_pct
32 
33 from trans1 a
34 left join trans1 (where=( service='TOTAL')) b
35 on a.table=b.table;
ERROR: Ambiguous reference, column Active is in more than one table.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
36 
37 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.00 seconds

Re: Using proc report

PaigeMiller — Fri, 12 Apr 2024 15:19:51 GMT

When you join two (or more) tables in SQL, and there is a variable (in this case ACTIVE) that exists in both tables that has the exact same name in each table, you have to refer to the variable as a.ACTIVE or b.ACTIVE so that SQL knows which one you mean. Referring to the variables as ACTIVE is ambiguous, SAS doesn't know which one you mean, and so you get this error message.

Re: Using proc report

CathyVI — Fri, 12 Apr 2024 15:43:55 GMT

@PaigeMiller

I am getting an error here. please what am I doing incorrectly

proc sql;
create table test4 as
select a.*, b.Active as Active_total, 
	(b.Active/Active_total)*100 as Active_pct

from test3 a
	left join test3 b 
(where=( b.serv_ord='TOTAL')) 
		and a.table=b.table;

quit;

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 (b.Active/Active_total)*100 as Active_pct
32
33 from test3 a
34 left join test3 b
35 (where=( b.serv_ord='TOTAL'))
_
73
76
ERROR 73-322: Expecting an ON.

ERROR 76-322: Syntax error, statement will be ignored.

36 and 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.00 seconds

Note:

Re: Using proc report

PaigeMiller — Fri, 12 Apr 2024 15:57:42 GMT

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. And since it is a DATA set option, you want the proper syntax here, which is

(where=(serv_ord='TOTAL'))

Re: Using proc report

CathyVI — Fri, 12 Apr 2024 16:52:18 GMT

@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

Re: Using proc report

PaigeMiller — Fri, 12 Apr 2024 16:56:28 GMT

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.

Re: Using proc report

CathyVI — Fri, 12 Apr 2024 17:11:27 GMT

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

Re: Using proc report

PaigeMiller — Fri, 12 Apr 2024 17:17:47 GMT

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.

Re: Using proc report

CathyVI — Fri, 12 Apr 2024 17:58:13 GMT

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