The SAS Output Delivery System and reporting techniques

PROC REPORT removing random values from ORDER variable

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 12
Accepted Solution

PROC REPORT removing random values from ORDER variable

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.


Accepted Solutions
Solution
‎07-22-2015 08:09 PM
SAS Super FREQ
Posts: 8,868

Re: PROC REPORT removing random values from ORDER variable

Posted in reply to MFLoGrasso

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


All Replies
Super User
Posts: 11,343

Re: PROC REPORT removing random values from ORDER variable

Posted in reply to MFLoGrasso

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?

Occasional Contributor
Posts: 12

Re: PROC REPORT removing random values from ORDER variable

Posted in reply to MFLoGrasso

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
Solution
‎07-22-2015 08:09 PM
SAS Super FREQ
Posts: 8,868

Re: PROC REPORT removing random values from ORDER variable

Posted in reply to MFLoGrasso

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

Occasional Contributor
Posts: 12

Re: PROC REPORT removing random values from ORDER variable

Posted in reply to Cynthia_sas

Cynthia,

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

Marc

Super User
Super User
Posts: 7,077

Re: PROC REPORT removing random values from ORDER variable

Posted in reply to MFLoGrasso

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?

.

Occasional Contributor
Posts: 12

Re: PROC REPORT removing random values from ORDER variable

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

SAS Super FREQ
Posts: 8,868

Re: PROC REPORT removing random values from ORDER variable

Posted in reply to MFLoGrasso

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

Occasional Contributor
Posts: 12

Re: PROC REPORT removing random values from ORDER variable

Posted in reply to Cynthia_sas

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

🔒 This topic is solved and locked.

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

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