BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
robertrao
Quartz | Level 8


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

1 ACCEPTED SOLUTION

Accepted Solutions
Cynthia_sas
SAS Super FREQ

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

22 REPLIES 22
Reeza
Super User

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
Quartz | Level 8


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
Super User

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
Quartz | Level 8

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
Super User

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

robertrao
Quartz | Level 8

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
Super User

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
Quartz | Level 8

Exactly

Reeza
Super User

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
SAS Super FREQ

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
Quartz | Level 8

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
SAS Super FREQ

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
Quartz | Level 8

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
Quartz | Level 8

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-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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