topic proc report sorting in SAS Programming

proc report sorting

pacman94 — Thu, 25 Feb 2021 17:24:06 GMT

I have the following dataset which is sorted by SEQ because that is the order i would like to keep for the CSV output

proc sort data = test; by seq;run;

Group1	Group 2	Value1	Value2	Value3	SEQ
Total		12	4	16	1
ADE	SDF	5	1	2	2
EFE	ERW	1	1	8	3
DSF	DGR	1	1	1	4
DFD	WER	5	1	5	5

However when I proc report export to the CSV, it shows 'total' in the bottom 'seq' sorting is gone.

ods csv file = 'filepath\test.csv.';

proc report data = test nowindows headline headskip;

column group1 group2 value1 value2 value3;

define group1/display = "Group 1" width =14;

define group2/display = "Group 2" width =14;

define value1 /display = "Value 1" width =14;

define value2/display = "Value 2" width =14;

define value3/display = "Value 3" width =14;

run;

Re: proc report sorting

ballardw — Thu, 25 Feb 2021 17:28:10 GMT

Since you did not provide any "order" information SAS used the default rules, which are generally going to be the formatted appearance of columns from left to right.

CSV has nothing to do with this.

Try

proc report data = test nowindows headline headskip;
   column seq group1 group2 value1 value2 value3;
   define seq / order noprint;
   define group1/display = "Group 1" width =14;
   define group2/display = "Group 2" width =14;
   define value1 /display = "Value 1" width =14;
   define value2/display = "Value 2" width =14;
   define value3/display = "Value 3" width =14;

run;

Re: proc report sorting

FreelanceReinh — Thu, 25 Feb 2021 18:01:59 GMT

Hi @pacman94,

The advantage of using SEQ as an order variable (as suggested by @ballardw) is that you don't need to sort the dataset in advance. If it is sorted by SEQ, however, you don't need to mention SEQ in the PROC REPORT step. Since all report items are display variables, the report should reflect the order of observations in the dataset. Maybe your last PROC REPORT step didn't run properly because of the invalid equal signs after "display" (did you check the log?) and that's why in your case (seemingly) "it shows 'total' in the bottom 'seq' sorting is gone."

Re: proc report sorting

Cynthia_sas — Fri, 26 Feb 2021 03:28:06 GMT

And, in addition to @FreelanceReinhard's suggestion, keep in mind that your options such as headline, headskip and WIDTH= are ignored by ALL ODS destinations, these are LISTING only options and not relevant to ODS CSV.
Cynthia