DATA Step, Macro, Functions and more

Report Basics

Accepted Solution Solved
Reply
Super Contributor
Posts: 1,041
Accepted Solution

Report Basics


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


Accepted Solutions
Solution
‎12-19-2012 10:01 PM
SAS Super FREQ
Posts: 8,864

Re: Report Basics

Posted in reply to robertrao

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


creating_rateord.png

View solution in original post


All Replies
Super User
Posts: 19,789

Re: Report Basics

Posted in reply to robertrao

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

Super Contributor
Posts: 1,041

Re: Report Basics


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

Super User
Posts: 19,789

Re: Report Basics

Posted in reply to robertrao

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.

Super Contributor
Posts: 1,041

Re: Report Basics

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

Super User
Posts: 19,789

Re: Report Basics

Posted in reply to robertrao

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

Super Contributor
Posts: 1,041

Re: Report Basics

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

Super User
Posts: 19,789

Re: Report Basics

Posted in reply to robertrao

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?

Super Contributor
Posts: 1,041

Re: Report Basics

Exactly

Super User
Posts: 19,789

Re: Report Basics

Posted in reply to robertrao

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.

SAS Super FREQ
Posts: 8,864

Re: Report Basics

Posted in reply to robertrao

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

Super Contributor
Posts: 1,041

Re: Report Basics

Posted in reply to Cynthia_sas

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

SAS Super FREQ
Posts: 8,864

Re: Report Basics

Posted in reply to robertrao

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

Super Contributor
Posts: 1,041

Re: Report Basics

Posted in reply to Cynthia_sas

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

Super Contributor
Posts: 1,041

Re: Report Basics

Posted in reply to Cynthia_sas

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??????

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 22 replies
  • 484 views
  • 6 likes
  • 4 in conversation