Solved: Re: Report Basics

robertrao · Posted 12-18-2012 04:44 PM

Hi ,

I have variables like shown

Code Description Discharges numer denom readmit rate

aaa headache 500 10 150 33.33

bbb vomits 777 7 700 7.7

ccc bodypain 350 22 400 19.25

define code/order=freq descending

By using the above define stmnt I am getting in the descending order of discharges as shown below???

bbb vomits 777 7 700 7.7

aaa headache 500 10 150 33.33

ccc bodypain 350 22 400 19.25

HOW CAN I GET???

aaa headache 500 10 150 33.33

ccc bodypain 350 22 400 19.25

bbb vomits 777 7 700 7.7

Cynthia_sas · Posted 12-19-2012 10:01 PM

Hi:

OK...here's something to study on. I don't actually think you need ORDER=FREQ DESCENDING. Take a look at this example -- it uses SASHELP.CLASS and divides height.sum by weight.sum -- sort of a silly ratio, but shows division in the creation of the new "helper" variables RATEORD and RRATE. I did not hide RATEORD in the #3 report, but after you understand what's happening, you can use NOPRINT:

ods _all_ close;

ods listing;

proc report data=sashelp.class nowd out=rateout;

title '1) Make 2 "extra" variables';

column age height weight rrate rateord;

define age / group;

define height /sum;

define weight/ sum;

define rrate / computed;

define rateord / computed;

compute rrate;

rrate = height.sum / weight.sum;

endcomp;

compute rateord;

rateord = height.sum/weight.sum;

endcomp;

run;

proc print data=rateout;

title '2) what is in RATEOUT dataset';

run;

proc sort data=rateout;
by descending rateord age;
run;

ods listing close;

ods html file='c:\temp\rateout.html' style=sasweb;
proc report data=rateout nowd;
title '3) Use RATEORD for ordering and use RRATE with % format';
column rateord age height weight rrate;
define rateord / order order=data /* noprint */;
define age / order;
define height / sum;
define weight/sum;
define rrate / f=percent9.2;
rbreak after / summarize;
compute rrate;
    if _break_ = '_RBREAK_' then do;
       ** calculate new overall rate for summary line;
       ** by dividing, or else rrate will be just added up;
       rrate.sum = height.sum / weight.sum;
    end;
endcomp;
run;
ods html close;
ods listing;
title;

Note that I did not multiply by 100 in my formula - -there's no point because when I use the PERCENT format on RRATE, the decimal point will be shifted. If you don't want to display the % sign, then go ahead and multiply by 100. For situations like this, I prefer to use PROC SORT to get the sort order I want and then just use ORDER=DATA on the DEFINE statement. You did not say whether you wanted an "overall" ratio, so, just in case, you will want that, I put an RBREAK and a COMPUTE in the #3. If you don't want that, then just get rid of it.

cynthia

View solution in original post

Reeza · Posted 12-18-2012 04:54 PM

What are you sorting by?

If you have preferred sort the best method in SAS is to put that variable as numbers in the sort order and then apply a format to the data.

A totally different example but same procedure here:

Sorting out your panelled graphs (part 1) - Graphically Speaking

robertrao · Posted 12-18-2012 05:07 PM

Hi,

Thanks for the reply. I calculate the readmit rate in the proc report itself.So I am not doing any sort for the dataset

Also I ncannot write a format in this case because there are hundreds of records. I jus showed three of them

Regards

Reeza · Posted 12-18-2012 05:13 PM

I'm assuming your question is about the order of the data ie sorting, if not can you clarify?

I see how you want it, but couldn't tell you why or what rule you're trying to implement.

robertrao · Posted 12-18-2012 05:19 PM

Ok,

The readmit rate variable you see is got from the proc report step.

I used a compute block to do it:

numer.sum/denom.sum

Also readmit rate is not a group ,order or across variable so it is not letting me to use descending option in the proc report

Regards

Reeza · Posted 12-18-2012 05:23 PM

Still don't understand what you want....

robertrao · Posted 12-18-2012 05:31 PM

Okie.The above table is not the dataset but the output from a proc report...in which i am using computed block to calculate the readmit rates with the above mentioned formulae.

So the report i got should be changed into the Decreasing order of Percentages(i mean readmit rate)...

it is in the irregular order

Hope that helps you with some idea

Regards

Reeza · Posted 12-18-2012 05:36 PM

So, you're reporting data using proc report. In the procedure you calculate a field, readmit rate, and you would like the report sorted by the calculated field in descending order?

robertrao · Posted 12-18-2012 05:38 PM

Exactly

Reeza · Posted 12-18-2012 05:54 PM

I get the same error in Proc report.

Personally, I prefer to do the calculations outside of report and use it as a display tool instead.

Cynthia_sas · Posted 12-18-2012 06:02 PM

Hi:

You cannot compute a variable and also order by it in the same PROC REPORT step. Your best bet is to compute the readmit rate outside of PROC REPORT and then make an ordering variable or other wise order outside of report. Look at my example 3 in the "Creating Complex Reports" paper (http://www2.sas.com/proceedings/forum2008/173-2008.pdf page 11 -- the code can be found Technical Papers and Presentations made by SAS staff in the section for 2008).

cynthia

robertrao · Posted 12-18-2012 09:35 PM

I could not get the exact code after scanning thru the 2008 presentations.

basically I have around 15k patients who are grouped based on CODE variable. Under each code there are several thousands discharged.we have again two variables NUMER and DENOM which have 1or 0 as values . How do we do the

numer.sum/denom.sum in the data step under each code???

the above posts give some clarity. Let me know if you require any further info

thanks

Cynthia_sas · Posted 12-19-2012 03:35 PM

Hi:

Did you download the zip file??? The data that is used for the #3 example is SASHELP.SHOES. Look at my example, I had to make 2 passes through the data -- the first pass summarized the data and created a variable that could be used for ordering. Granted, in my program, I was NOT calculating a new variable, but the concept is still the same.

But I have a question about your posted example, how did you derive your readmit rate??? For example, you show this:

Code Description Discharges numer denom readmit rate

aaa headache 500 10 150 33.33

and say that your formula is numer.sum/denom.sum, but when I divide 10/150 for just this row , my calculator gives me: 0.066666667 and not .3333, so can you elaborate? It doesn't look like the rate of the grand total, either.

cynthia

robertrao · Posted 12-19-2012 03:41 PM

Hi,

Thanks a lot for your persual.Yes you are right .i mite have done that in a hurry

The mistake i commited was that i multiplied the product with the number of discharges.I dont know why I did but thats wrong.i aplologise

Regards

robertrao · Posted 12-19-2012 03:47 PM

Let me also tell you the progress i made so far........

Like you taught me in the earlier posts and said we cannot order a computed variable.........I created a OUT=dataset in a dummy PROC REPORT which have the rate calculated as

rate=(readmit_numer.sum/readmit_denom.sum)*100;

Now I have the percentages in the dataset.......And so I can order then by

define rate/order=freq descending in the NEXT PROC REPORT STEP

IS THAT RIGHT??????

SAS Innovate 2025: Save the Date

SAS Training: Just a Click Away