Proc Report and order

[GKati]

Dear SAS users,

I am trying to use PPOC REPORT with ORDER. The input dataset (parmest) is sorted already by numord. I want the output table (TABLE_SHORT) to be reported in order of numord, but it is coming out in completely different order.  See input and output datasets attached.

What am I doing wrong?

proc report data=parmest nowd out=table_short;
column variable GROUPS 
('Total-Model 1' Estimate1 StdErr1 Probt1) 
('Total-Model 2' Estimate2 StdErr2 Probt2)
('Total-Model 3' Estimate3 StdErr3 Probt3) 
('Gen. Hosp.-Model 3' Estimate4 StdErr4 Probt4) 
('UMC Hosp.-Model 3' Estimate5 StdErr5 Probt5)
('TC Hosp.-Model 2' Estimate6 StdErr6 Probt6)
('TC Hosp.-Model 3' Estimate7 StdErr7 Probt7)
;
define groups/group noprint;
define Variable /order order=data display;
define estimate1 - estimate7 /order order=data display 'Estimate';
define StdErr1 - StdErr7/order order=data display format=stderrf.;
define probt1 - probt7 /order order=data display;
run;

[ballardw]

If you want the data controlled by a variable (numord) and you have other variables with the ORDER role (all of your variables in this case) then that variable would have to be the first one in the column statement, be assigned the role order in define statement. If you do not want the variable to actually appear in the table then use the option NOPRINT in the define statement. Or change the ORDER in your existing define blocks to only the display.

Using order order=data display in a define isn't a good idea as putting two roles, order and display, might yield unexpected results.

Better for clarity if no other reason would be

order=data display

Many users here don't want to download Excel files because of virus potential, others have such things blocked by security software. Also if you give us Excel we have to create a SAS data set and due to the non-existent constraints on Excel data cells the result we end up with may not have variables of the same type (numeric or character) and even values.

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

[Cynthia_sas · Accepted solution]

Hi:
In addition to the very thoughtful comments from @ballardw, please keep in mind that you can only have 1 usage on your DEFINE statement. You have 2 usages:
define xxx /order order=data display;

ORDER is a usage and DISPLAY is a usage. You can only have 1 usage. You are getting DISPLAY usage and you want ORDER usage. Take off DISPLAY and see what happens. But I am still not sure you are going to get the order you want because you don't show your PROC SORT and you don't have enough DEFINE statements for any of your other variables in the COLUMN statement.

Even if I did open your Excel file, I don't typically write a program to read the file into SAS form because there's no guarantee that the variables would come in as the same types as you have them.

cynthia