hello,
I'm having trouble sorting and collapsing my data in a proc report. I have created a sample dataset. Prior programming has replaced any frequency percent <3.9% as 'other' in the drugcombination variable. In the report, I want to do the following:
1) to collapse the 'other' category within each combogroup.
2) to place 'Other' combogroup at the end and 'other' drugcombinations at the end within combogroup. I believe I have done this with the dummy sort variables I have created.
3) I want to put the drugcombinationchemo in an order based on the descending value of the percent column (while still keeping 'other' at the end if possible).
I have tried various ways to show the percent column in descending order plus collapsing 'other' and nothing seems to work. Do I need to do a proc tabulate to sum the 'other' values and then do proc report?
I am getting the following output which is not accomplishing item 1 and item 3 above.
SAS Output
Dar Mono | daratumumab | 100.00 |
IMiD Mono | lenalidomide | 94.29 |
pomalidomide | 1.43 | |
thalidomide | 4.29 | |
PI Mono | bortezomib | 93.88 |
carfilzomib | 5.94 | |
other | 0.17 | |
PI-Cyclo± | bortezomib - cyclophosphamide | 99.25 |
carfilzomib - cyclophosphamide | 0.75 | |
PI-Dox± | bortezomib - doxorubicin lipos | 100.00 |
PI-IMiD | bortezomib - lenalidomide | 98.36 |
bortezomib - pomalidomide | 1.09 | |
other | 0.55 | |
Other | bortezomib - daratumumab | 17.24 |
cyclophosphamide | 10.34 | |
elotuzumab | 13.79 | |
elotuzumab - lenalidomide | 6.90 | |
investigational drugs | 34.48 | |
other | 3.45 | |
Sorry, not here to write an output for you. What I will say is that when I do these types of output I make sure the dataset looks like the output I want. So you would sort the data - based on some ID you assign in a datastep - in the way the output should look, you don't have to report the ID, just make sure the column has order=data option on. This fixes how the data gets displayed. In fact for all my reports I have id's for each row stating what page it should be on, and then one id for each subgroup within page, this way when I report I just use the data order to report.
Hi:
In looking at all your variables, and changing your code slightly, it seems that between DRUGSORT and DRUGSORT2, you have too many ways for the "OTHER" category to be put on the report. Some OTHER rows have DRUGSORT2=0 and DRUGSORT=1 and then other rows have DRUGSORT2=1 and DRUGSORT=0 and DRUGSORT2=1 and DRUGSORT=1 -- so there are 3 possible ways for an "OTHER" row to be placed on the report.
I'm not sure of your logic, so don't know what to suggest. Using both DRUGSORT2 and DRUGSORT on the report seems to lead closer to what you say you want, but since I am not entirely sure of what you want to see from your description I don't know. Whatever comes first on the COLUMN statement is going to control the order of the rows. So if you only put DRUGSORT in the COLUMN statement and NOT DRUGSORT2, you may not get the results you want.
Here's a little test I ran changing a few things in your PROC REPORT to show the impact of having both DRUGSORT2 and DRUGSORT in the column statement. I also have some questions about the data values that look wonky to me.
cynthia
Cynthia,
Thanks for taking a look at this. There was a step I didn't identify which might explain the 'other' issue.
There will always be an OTHER combogroup (DRUGSORT=1 IF COMBOGROUP=OTHER OR ELSE IT IS 0). Within each combogroup category, including this OTHER combo group, there are many drugs. The percentages should add to 100% within each combogroup. We only want to show the drugs that most contribute to the category so drug names have been changed to "other" if the share is small with the intent of collapsing the 'other' category and summing the percents for that group. This means that there will even be 'other' drugcombinationchemo within the OTHER combogroup. So, if I add drugsort2 to the columns and define it as an ORDER option I will get "Other" combogroup and "other" drugcombinationchemo showing up at the end of the table. This is ok but the problem is I really want the percents to be in descending order WITHIN combogroup too. For example the following code gets me there ALMOST. If you run it you will see that for the IMiD Mono category pomalidomide is only 1% and is listed before thalidomide which is 4%. If I have to choose between "other" being at the end of each category OR values be in descending order, I'd rather have the values in descending order.
The other problem which can be fixed with a proc tabulate is that the multiple 'others' need to be summed and collapsed into one row within each combogroup category. I was hoping this could be done in the proc report to avoid unnecessary multiple steps but maybe it's not possible ??? What I am noticing is that it only shows one of the 'other' rows in the category and blanks out the others.
Your sort order is not in the right order and I believe this is because drugsort needs to be before drugsort2. If this helps identify my problem better and you think of anything please pass it along. Thanks!
proc report data=have nowd
style(report)=[background=ltgray ]
style(column)=[background=gwh]
style(header)=[background=ltgray foreground=white];
column drugsort combogroup drugsort2 drugcombinationchemo percent ;
define drugsort /order order=internal noprint;
define drugsort2 /order order=internal noprint;
define combogroup /order order=data group 'Combination Group' ;
define percent /analysis sum order order=internal;
define drugcombinationchemo /group 'Combination' ;
run;
The 'have' sample dataset was originally created from a proc freq using ods output to a dataset. I appreciate you helping me to understand the proc report better but it looks like I'd be better off doing my reporting in Excel. However, just so I don't leave you hanging, this is what I want to end up with (created in Excel from the SAS 'have' dataset in my example).
Combination Group | Combination | Percent |
Dar Mono | daratumumab | 100 |
IMiD Mono | lenalidomide | 94.29 |
thalidomide | 4.29 | |
pomalidomide | 1.43 | |
PI Mono | bortezomib | 93.88 |
carfilzomib | 5.94 | |
other | 0.17 | |
PI-Cyclo± | bortezomib - cyclophosphamide | 99.25 |
carfilzomib - cyclophosphamide | 0.75 | |
PI-Dox± | bortezomib - doxorubicin lipos | 100 |
PI-IMiD | bortezomib - lenalidomide | 98.36 |
bortezomib - pomalidomide | 1.09 | |
other | 0.55 | |
Other | investigational drugs | 34.48 |
bortezomib - daratumumab | 17.24 | |
elotuzumab | 13.79 | |
cyclophosphamide | 10.34 | |
elotuzumab - lenalidomide | 6.9 | |
other | 17.25 | |
Consider a proper sort and data step sometimes.
proc sort data=have; by drugsort combogroup descending percent; run; data want; set have; by drugsort combogroup DrugCombinationChemo notsorted; retain otsum; if first.DrugCombinationChemo then otsum=.; otsum= sum(otsum,percent); if last.DrugCombinationChemo then do; percent=otsum; output; end; run; proc print data=want (drop=drugsort drugsort2 otsum) noobs; run;
Now I am confused again. I thought you wanted ALL the "other" to be at the bottom and yet, you show other under PI-IMiD and under PI Mono. But that is very doable with PROC REPORT just changing your code a bit.
Using your sample data, but without regard to DRUGSORT2, and only changing the usages to be consistent (usage of GROUP does collapsing), this is what I get:
cynthia
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: