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

So, I have a simple table that I would like to present using the following code:

proc report nowd data=wt_anly_cmp;

  column Campus cohort coh_pct Withdrawals wd_rate sig pct_ratio excess wd_Rate_py Pct_Ratio_py;

  define sig /left;

  define excess /order order=internal descending;

  define wd_rate /style(column)={backgroundcolor=verylightgray};

  run;

Unfortunately, when I run the report, some values in the sorted column disappear (EXCESS in line 04).  Note that the observations are in the correct sort order that the values not gone missing.  I have tried changing the ORDER variable, and it repeats itself, seemingly randomly removing values from the new ORDER variable after properly sorting the observations.  I tried applying a format in the DEFINE statement to no avail, and changing the ORDER variable moves the removed values to different rows, so I really don't know what is causing the problem.

1 ACCEPTED SOLUTION

Accepted Solutions
Cynthia_sas
SAS Super FREQ

Hi:

If you define EXCESS as an ORDER, then the repetitious display of values will be suppressed. For example, this code:

 

data class;

   set sashelp.class;

   ** make some people the same height;

   if name in ('Barbara', 'Judy', 'Carol','Henry', 'Jeffrey')

      then height = 67.5;

run;

proc report data=class nowd;

  title '1) Default Behavior';

  column sex height weight name;

  define sex / order;

  define height / order descending;

  define weight / display;

  define name / display;

run;

 

proc report data=class nowd;

  title '2) Changed Behavior using a COMPUTEd item';

  column sex height showht weight name;

  define sex / order;

  define height / order descending;

  define showht / computed;

  define weight / display;

  define name / display;

  compute showht;

    if height ne . then do;

      holdit = height;

      showht = height;

     end;

    else if height = . then showht=holdit;

  endcomp;

run;

Produces these results:

Default:

default_report_order_behavior.png

Using Compute Block:

revised_report_behavior.png

View solution in original post

8 REPLIES 8
ballardw
Super User

Some input data, the results you are getting and the desired results would be helpful. Also, are there any warnings or notes about that variable in the log?

MFLoGrasso
Obsidian | Level 7

No warnings or notes.  Here is my data (campus information removed).

CampusTERM_CODE_TYPE__PAGE__TABLE_CohortCPctWithdrawalswd_RatePct_RatioExcesswd_Rate_pyPct_Ratio_pycoh_pctzpvalsig
A20153011111622.0679091141710.49% 0.77-517.97% 0.912.07% -1.2023844870.229214597
B20153011112913.7145774834816.49% 1.20817.54% 0.893.71% 1.4077647080.159200754
C20153011112683.420985448248.96% 0.65-1311.20% 0.573.42% -2.3024392010.021310416*
D20153011113504.4677048765415.43% 1.13623.80% 1.204.47% 0.9566220550.338758053
E20153011114275.4505999494911.48% 0.84-1023.05% 1.165.45% -1.3802573520.167507421
F20153011113344.2634669396619.76% 1.442027.51% 1.394.26% 3.2858099140.001016896**
G20153011113284.1868777133711.28% 0.82-813.21% 0.674.19% -1.3065600910.191362158
H20153011112843.6252233854616.20% 1.18722.68% 1.153.63% 1.241531840.214409343
I20153011114365.5654837895813.30% 0.97-220.09% 1.015.57% -0.2540697360.799441683
J20153011113144.0081695174815.29% 1.12521.32% 1.084.01% 0.8292811280.406945347
K20153011114685.97395966313228.21% 2.066834.68% 1.755.97% 9.4019319935.35702E-21***
L20153011113023.854991065134.30% 0.31-2812.46% 0.633.85% -4.8458690481.26059E-06***
M20153011111,32116.8623946926319.91% 1.458222.19% 1.1216.86% 7.1846043946.74021E-13***
N20153011113985.0804186884210.55% 0.77-1314.79% 0.755.08% -1.879223170.060214027
O20153011114365.565483789419.40% 0.69-1915.15% 0.775.57% -2.689752120.007150511**
P2015301111260.331886648311.54% 0.84-116.00% 0.810.33% -0.3223671170.747174592
Q20153011113774.812356395266.90% 0.50-2616.84% 0.854.81% -3.9417948928.08741E-05***
R20153011113033.8677559363712.21% 0.89-529.31% 1.483.87% -0.773306280.43934114
S20153011114545.795251468429.25% 0.67-2014.52% 0.735.80% -2.8454307170.004435141**
T20153011115557.084503447285.05% 0.37-489.69% 0.497.08% -6.1563399737.44453E-10***

And the result I'm seeing.  No warnings or notes in the log.  Note the blanks in the Excess column for campuses R and N, highlighted in red below.  I did just notice that the formatted values for these entries are exactly the same as the line before it (-5 and -13, respectively).  On a whim, I just tried adding the SHOWALL option to the PROC line with no effect, unfortunately.

            

CampusCohortPercent of CohortWithdrawalsWithdrawal RateSignifPercent RatioExcessPrior Year Withdrawal RatePrior Year Percent Ratio
M  1,321  16.86%   263  19.91% ***  1.45  82  22.19%   1.12
K  468  5.97%   132  28.21% ***  2.06  68  34.68%   1.75
F  334  4.26%   66  19.76% **  1.44  20  27.51%   1.39
B  291  3.71%   48  16.49%   1.20  8  17.54%   0.89
H  284  3.63%   46  16.20%   1.18  7  22.68%   1.15
D  350  4.47%   54  15.43%   1.13  6  23.80%   1.20
J  314  4.01%   48  15.29%   1.12  5  21.32%   1.08
P  26  0.33%   3  11.54%   0.84  -1  16.00%   0.81
I  436  5.57%   58  13.30%   0.97  -2  20.09%   1.01
A  162  2.07%   17  10.49%   0.77  -5  17.97%   0.91
R  303  3.87%   37  12.21%   0.89   29.31%   1.48
G  328  4.19%   37  11.28%   0.82  -8  13.21%   0.67
E  427  5.45%   49  11.48%   0.84  -10  23.05%   1.16
C  268  3.42%   24  8.96% *  0.65  -13  11.20%   0.57
N  398  5.08%   42  10.55%   0.77   14.79%   0.75
O  436  5.57%   41  9.40% **  0.69  -19  15.15%   0.77
S  454  5.80%   42  9.25% **  0.67  -20  14.52%   0.73
Q  377  4.81%   26  6.90% ***  0.50  -26  16.84%   0.85
L  302  3.85%   13  4.30% ***  0.31  -28  12.46%   0.63
T  555  7.08%   28  5.05% ***  0.37  -48  9.69%   0.49
Cynthia_sas
SAS Super FREQ

Hi:

If you define EXCESS as an ORDER, then the repetitious display of values will be suppressed. For example, this code:

 

data class;

   set sashelp.class;

   ** make some people the same height;

   if name in ('Barbara', 'Judy', 'Carol','Henry', 'Jeffrey')

      then height = 67.5;

run;

proc report data=class nowd;

  title '1) Default Behavior';

  column sex height weight name;

  define sex / order;

  define height / order descending;

  define weight / display;

  define name / display;

run;

 

proc report data=class nowd;

  title '2) Changed Behavior using a COMPUTEd item';

  column sex height showht weight name;

  define sex / order;

  define height / order descending;

  define showht / computed;

  define weight / display;

  define name / display;

  compute showht;

    if height ne . then do;

      holdit = height;

      showht = height;

     end;

    else if height = . then showht=holdit;

  endcomp;

run;

Produces these results:

Default:

default_report_order_behavior.png

Using Compute Block:

revised_report_behavior.png

MFLoGrasso
Obsidian | Level 7

Cynthia,

I'll have to try that when I'm back at my desk in the morning.

Marc

Tom
Super User Tom
Super User

That is what ORDER is DESIGNED to do. 

proc report data=sashelp.class;

  where age < 13 ;

  column age name ;

  define age / order ;

run;

  

  Age Name

   11 Joyce

       Thomas

   12 James

       Jane

       John

       Louise

       Robert

Why not just use PROC SORT to set the data in right order?

.

MFLoGrasso
Obsidian | Level 7

Tom,

Unfortunately, I have many tables with the same issue, as I am actually analyzing one variable in many different ways, and I was looking for a way to streamline my code as opposed to a series of PROC SORTs.

Marc

Cynthia_sas
SAS Super FREQ

Hi:

  My code example shows how to work around the issue of using EXCESS as an ORDER item on the report. I posted the code and screen shots. I think you want the second example.

cynthia

MFLoGrasso
Obsidian | Level 7

That's what i went for, and it worked like a charm.  THANKS!

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 8 replies
  • 1943 views
  • 5 likes
  • 4 in conversation