topic Re: Proc report to calculate rates in SAS Programming

Proc report to calculate rates

AbuYusuf — Fri, 21 Jun 2019 15:29:18 GMT

Hi,

I am trying to do a simple thing: calculate discharge rate by disease category and year. I am trying to figure out how to use proc report to do that, and I can't get it to work. My data has these 4 variables: disease, year, number of discharges, and count (population by age and sex). Here is the code I am using:

proc report data=rmtwork.table1;
title "Mental health discharge rate per 100,000 by diagnostic category.";
column disease discharges count year, poprate;
define disease / group;
define year / across order=freq width=4 'Year';
define discharge / 'Number of discharges';

define poprate / computed format=6.5 'Rate per 100,000';

compute poprate;
poprate = discharges.sum/count.sum*100000;
endcomp;
run;

The code generates missing values. It works without the across statement, but not when I try to generate these rates by year.

I'd be grateful if you could help me sort this out!

Thank you,

Re: Proc report to calculate rates

Reeza — Fri, 21 Jun 2019 15:35:10 GMT

Can you provide sample data and show what you want as output? Fake data is totally fine.

Re: Proc report to calculate rates

AbuYusuf — Fri, 21 Jun 2019 15:47:44 GMT

Thank you for your response!

I use proc tabulate to create this table:

	2010	2011	2012	2013	2014	2015	2016	2017
	discharges	count	discharges	count	discharges	count	discharges	count	discharges	count	discharges	count	discharges	count	discharges	count
Excluded	56	25964434	318	25964434	412	25964434	408	25964434	172	25964434	90	25964434	129	25964434	27	25964434
Organic disorders	1275	26042315	1854	26042315	1928	26042315	2062	26042315	2166	26042315	2381	26042315	2450	26042315	681	26042315
Substance-related disorders	1930	26075667	2656	26075667	2805	26075667	2957	26075667	3254	26075667	3583	26075667	3961	26075667	979	26075667
Schizophrenic and psychotic disorders	2294	26075667	3099	26075667	3157	26075667	3212	26075667	3193	26075667	3177	26075667	3472	26075667	901	26075667
Mood disorders	3252	26075667	4427	26075667	4572	26075667	4583	26075667	4574	26075667	4513	26075667	4730	26075667	1212	26075667
Anxiety disorders	432	26042315	639	26042315	725	26042315	771	26042315	797	26042315	849	26042315	721	26042315	156	26042315
Personality disorders	246	26075667	323	26075667	317	26075667	345	26075667	394	26075667	455	26075667	629	26075667	188	26075667
Other disorders	1714	26075667	2448	26075667	2745	26075667	2762	26075667	2903	26075667	3025	26075667	2766	26075667	650	26075667

Now I am trying to use proc report to create this:

	2010	2011	2012	2013	2014	2015	2016	2017
	rate	rate	rate	rate	rate	rate	rate	rate
Excluded
Organic disorders
Substance-related disorders
Schizophrenic and psychotic disorders
Mood disorders
Anxiety disorders
Personality disorders
Other disorders

Re: Proc report to calculate rates

Reeza — Fri, 21 Jun 2019 15:50:52 GMT

I can almost guarantee that PROC TABULATE did not generate a table like that into a data set. What does the data set look like?

Re: Proc report to calculate rates

ballardw — Fri, 21 Jun 2019 15:53:52 GMT

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712 will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

Re: Proc report to calculate rates

AbuYusuf — Fri, 21 Jun 2019 15:54:58 GMT

Why not?

proc tabulate data=rmtwork.table1;
format province province.;
format discatv2 disease.;

class discyear / order=unformatted missing;
class discatv2 / order=unformatted missing;

var LOS_N;
var population;

table discatv2, discyear*(LOS_N population);

run;

where discatv2 = disease, discyear = year, los_n = discharge, and population = count as I called them in the previous post.

It's a large administrative data set

Re: Proc report to calculate rates

AbuYusuf — Fri, 21 Jun 2019 15:55:38 GMT

thank you!

Re: Proc report to calculate rates

Reeza — Fri, 21 Jun 2019 15:58:23 GMT

PROC TABULATE gives you output in a displayed format, not in a table that you can use to feed into PROC REPORT. You can capture the data from PROC TABULATE using an OUT= or ODS OUTPUT statement but I don't see any indication of that. And the format doesn't match the displayed output.

EDIT: I would suggest providing a sample of the original data and showing what you expect as output.

Multiple edits.

@AbuYusuf wrote:

Why not?

proc tabulate data=rmtwork.table1;
format province province.;
format discatv2 disease.;

class discyear / order=unformatted missing;
class discatv2 / order=unformatted missing;

var LOS_N;
var population;

table discatv2, discyear*(LOS_N population);

run;

where discatv2 = disease, discyear = year, los_n = discharge, and population = count as I called them in the previous post.

It's a large administrative data set

Re: Proc report to calculate rates

Cynthia_sas — Fri, 21 Jun 2019 17:27:09 GMT

Hi:

I'm confused. It looks like you want to calculate POPRATE for each value of YEAR. So I would normally assume that you want the calculation to be based on the discharge and count for EACH separate YEAR. However, you only have POPRATE under YEAR. You don't have DISCHARGE and COUNT under YEAR.

Having data would help. I also think you will need absolute column numbers used for your COMPUTE block. Any variables under an ACROSS item that you are calculating must be referenced by absolute column number. I know there are previous postings about absolute column numbers.

Using some fake data starting from SASHELP.PRDSALE below shows what your original code would result in versus using absolute column numbers and putting discharges and count under each year.

After you are sure the calculations are correct (for report 2), then you could use NOPRINT on DISCHARGES and COUNT to hide them on the report. Note that I did not multiply by 100000 because I was really just trying to show the use of absolute column numbers and a simple division worked for that.

Cynthia

Re: Proc report to calculate rates

AbuYusuf — Fri, 21 Jun 2019 20:11:05 GMT

Hello Cynthia,

Thank you very much! That was exactly what I wanted to do. I tried putting discharges and count under year, but the code didn't work out (for another reason as I now understand), so I gave up on that approach.

Thank you very much!

Re: Proc report to calculate rates

Cynthia_sas — Fri, 21 Jun 2019 20:29:09 GMT

Hi: Glad it worked for you! Here's a paper about using ACROSS that may help you understand how it works: http://support.sas.com/resources/papers/proceedings14/SAS388-2014.pdf

Cynthia