Proc Report - Ordering on a computed column

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 16
Accepted Solution

Proc Report - Ordering on a computed column

Dear Communities,

Probably a very simple thing to do, but I can't work it out or find the correct answer online and so would appreciate some help.

I want to do a very simple display table using Proc Report.  The only novel element of this is that I want the table rows sorted by a variable which I am creating within the Proc Report using Compute in the Define statement.  The new variable is created just fine, but I can't figure out how to order on it.  Any ideas?

Thanks in advance,   Chris


Accepted Solutions
Solution
‎08-16-2013 08:33 AM
Super Contributor
Posts: 358

Re: Proc Report - Ordering on a computed column

Posted in reply to cb23_york

I honestly don't think you can order the data based on a computed value.


From your code - why not just create the new variable in a DATA step and then sort before you do the PROC REPORT?  Use the KISS rule.....

View solution in original post


All Replies
Trusted Advisor
Posts: 1,137

Re: Proc Report - Ordering on a computed column

Posted in reply to cb23_york

could you please provide the code you have written for this

we can sort the new variable created in the define statement you have used by puting order as below

define new variable / order computed;

Thanks,

jagadish

Thanks,
Jag
Occasional Contributor
Posts: 16

Re: Proc Report - Ordering on a computed column

Posted in reply to Jagadishkatam

Jagadish,

Thanks for your reply, the code is ..

     TITLE "Large Changes &yr. in General Classifications";

     PROC REPORT DATA = RC.Ref_costs_all OUT = RC.large_changes (DROP = _BREAK_);

          COLUMNS type setting sheet_name code units unadj_cost lag_units lag_unadj_cost dif;

          WHERE    observed = 1 AND

                          lag_units ~= 0 AND

                          year = &yr AND

                          ABS(unadj_qtpt - unadj_qt_1pt_1) >= &lrg_change;

          DEFINE type / DISPLAY;

          DEFINE setting / DISPLAY;

          DEFINE code / DISPLAY;

          DEFINE units / DISPLAY FORMAT=COMMA25. 'Act';

          DEFINE unadj_cost / DISPLAY FORMAT=NLMNY25.2 'Cost';

          DEFINE lag_units / DISPLAY FORMAT=COMMA25. 'Lag Act';

          DEFINE lag_unadj_cost / DISPLAY FORMAT=NLMNY25.2 'Lag Cost';

          DEFINE dif/ ORDER COMPUTED FORMAT=NLMNY25.2;

          COMPUTE dif;

                    dif = (units*unadj_cost) - (lag_units*lag_unadj_cost);

          ENDCOMP;

RUN;

which produces the following unsorted table

NonNHS

Outpatient

clfasff

110T

31

£75.42

84,654

£385.99

-£32,673,037.32

NonNHS

Outpatient

clfusff

110T

131

£77.26

104,555

£682.58

-£71,357,445.87

Trust

A&E Services

aandemsad

VB04Z

428,472

£210.42

258,079

£209.42

£36,112,506.09

Trust

A&E Services

aandemsna

VB07Z

1,047,032

£142.92

873,252

£139.40

£27,908,883.38

Trust

A&E Services

aandemsna

VB09Z

2,981,590

£91.47

2,507,909

£93.28

£38,796,731.58

Trust

Specialist Services

ccsalccu

XC06Z

436,367

£868.23

389,427

£883.03

£34,993,426.56

Trust

Chemo/Radiotherapy & High Cost Drugs

chemthpy_proc_dcrdn

SB10Z

56,328

£1,960.18

35,758

£2,164.87

£33,001,950.15

Trust

Chemo/Radiotherapy & High Cost Drugs

chemthpy_sda_dcrdn

SB97Z

441,297

£0.00

398,232

£85.07

-£33,879,022.96

Trust

Outpatient

clfusff

110N

2,457,894

£95.14

2,342,498

£85.91

£32,600,446.24

Trust

Outpatient

clfusff

H/A

204,575

£432.15

211,872

£535.40

-£25,028,845.08

Trust

Community Care

crt

CRTX

2,395,301

£74.57

815,208

£64.50

£126,037,127.89

Trust

Community Care

cscmo

CP60FO

343,333

£284.10

131,769

£316.51

£55,836,191.79

Trust

Community Care

cscnsn

CN202AF

758,543

£85.70

122,008

£95.67

£53,337,811.00

Trust

Community Care

cscnsn

CN203BAF

532,701

£73.67

162,826

£74.52

£27,109,605.03

Trust

Community Care

cscnsn

CN203CAF

483,575

£73.28

141,964

£61.88

£26,651,517.51

Trust

Community Care

cscnsn

CN204AF

641,525

£84.23

125,186

£116.88

£39,404,969.95

Trust

Community Care

cscnsn

CN213AF

727,258

£63.04

133,848

£63.91

£37,289,295.26

Trust

Community Care

cscnsn

CN215AF

1,000,984

£68.93

295,022

£53.91

£53,093,614.08

Trust

Community Care

cscnsn

CN217AF

650,783

£86.14

137,199

£43.47

£50,090,805.47

Trust

Community Care

csct

N5A1

3,764,570

£45.77

998,564

£46.56

£125,792,889.95

Trust

Community Care

csct

N5C1

780,753

£74.02

306,668

£73.85

£35,146,178.60

Trust

Community Care

csct

N6A1

739,304

£71.24

210,883

£66.13

£38,722,767.30

Trust

Community Care

csct

N6C1

406,588

£113.34

131,912

£101.63

£32,676,266.40

Trust

Community Care

csct

N7A1

470,364

£82.84

121,469

£74.11

£29,961,109.02

Trust

Community Care

csct

N7C1

1,777,097

£89.13

566,011

£83.28

£111,247,408.24

Trust

Community Care

csdn

CN301AF

24,159,757

£38.42

5,804,543

£38.32

£705,783,519.13

Trust

Community Care

csdn

CN301AN

2,482,202

£14.24

455,867

£16.72

£27,733,893.44

Trust

Community Care

cshvc

CN403CFO

7,095,108

£45.82

1,302,420

£39.58

£273,511,154.77

Trust

Community Care

cshvo

CN403FO

2,967,158

£50.61

954,841

£46.62

£105,646,363.98

Trust

Community Care

cshvpn

CN402

736,974

£61.56

237,174

£56.65

£31,933,950.32

Trust

Community Care

csshc

CH103CFO

1,920,855

£44.51

465,629

£38.40

£67,608,956.28

Trust

Community Care

cssnc

CN101

1,002,378

£92.89

467,496

£105.90

£43,607,003.80

Trust

Community Care

cssno

CN103FO

1,356,122

£47.01

239,346

£29.59

£56,675,657.30

Trust

Chemo/Radiotherapy & High Cost Drugs

hicostdrugs_op

XD38Z

175,951

£890.79

139,677

£845.41

£38,650,735.06

Trust

Chemo/Radiotherapy & High Cost Drugs

hicostdrugs_op

XD46Z

105,371

£966.05

65,462

£982.09

£37,503,926.48

Trust

Community Care

ocs

CN20

1,216,594

£95.70

372,689

£78.22

£87,281,843.35

Trust

Community Care

ocs

N800

749,442

£71.09

285,716

£69.54

£33,407,608.14

Trust

Community Care

ocs

N910

4,150,440

£40.22

810,152

£47.00

£128,861,760.07

Trust

Outpatient

oproc

HB99Z

55,636

£142.15

401,894

£144.60

-£50,203,849.15

Trust

Rehabilitation

rehab_csrs_level_1_bedday_apc

VC12Z

119,815

£288.12

21,471

£425.79

£25,379,002.71

Trust

Rehabilitation

rehab_csrs_level_1_bedday_apc

VC42Z

300,480

£260.03

89,286

£309.48

£50,500,920.79

Trust

Rehabilitation

rehab_nsrs_bedday_apc

VC04Z

228,557

£300.93

145,117

£293.55

£26,180,987.80

Trust

Rehabilitation

rehab_nsrs_bedday_apc

VC42Z

546,757

£246.91

299,588

£251.77

£59,574,901.15

Solution
‎08-16-2013 08:33 AM
Super Contributor
Posts: 358

Re: Proc Report - Ordering on a computed column

Posted in reply to cb23_york

I honestly don't think you can order the data based on a computed value.


From your code - why not just create the new variable in a DATA step and then sort before you do the PROC REPORT?  Use the KISS rule.....

Super Contributor
Posts: 394

Re: Proc Report - Ordering on a computed column

Posted in reply to cb23_york

OS/2 is right. In PROC REPORT, the rows are ordered before computed variables are computed.

🔒 This topic is solved and locked.

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

Discussion stats
  • 4 replies
  • 207 views
  • 3 likes
  • 4 in conversation