proc sql summary table-order of categories

[FreelanceReinh]

The NET operator is one of several operators available in PROC SQL for comparison of truncated strings, see Truncated String Comparison Operators. Two strings are considered "not equal truncated" (so to speak) if the shorter of the two does not equal the longer string, truncated to the length of the shorter string. For example, "abc" net "abd" and "abc" net "ac" are true, but "abc" net "ab" and also "a" net "abc" are false.

The Boolean values resulting from an expression like age net 'T' are treated as any other numeric value 0 or 1, e.g., in a numeric variable or as a result of a calculation. In your example, all records with age values starting with "T" -- there is only one: "TILL 18" -- take precedence over records with age values not starting with "T" because the Boolean values age net 'T' (0 or 1) are sorted in ascending numeric order: 0 first, 1 second, as usual.

The second item in the ORDER BY clause, which could also be written as

order by age net 'T', age

determines the sort order within the two groups defined by the first item. Here, it has no impact on the first group because a single record (with age="TILL 18") cannot be sorted in different ways. But it is relevant for the second group: The three character strings "18-19", "19-21" and "21+" are sorted in ascending alphabetical order, as intended.

For questions regarding the other procedures you mentioned please open a new thread because they are quite different from PROC SQL, which is the subject of this thread.

[PaigeMiller]

---

@Ronein wrote:

Hello

I want that the order of categories in desired output will be in same order as in proc format.

what is the way to do it please by using proc sql code as below?

One solution is to write proc format with (a) (b) (c) (d) and so on  but it doesnt look  nice in output

proc format;
value agefmt
low-18='(a) TILL 18'
18-19='(b) 18-19'
19-21='(c) 19-21'
21-high='(d) 21+'
;
Run;

Data have;
input ID age;
cards;
1 15
2 17
3 18
4 21
5 22
6 19
7 19
8 31
9 39
10 48
11 54
12 17
13 18
;
Run;


proc format;
value agefmt
low-18='TILL 18'
18-19='18-19'
19-21='19-21'
21-high='21+'
;
Run;
title;
PROC SQL;
	select put(age,agefmt.) as age ,
          count(*) as nr  ,
          calculated nr/(select count(*) as  total_nr from  have)*100 as pctN 
	from  have
	group by  calculated age 
;
QUIT;

---

Any procedure that will analyze this data ought to be able to put the results into numerical order without the (a) or (b) or whatever. PROC FREQ and PROC REPORT and PROC SUMMARY (and probably other procedures) all have an ORDER=INTERNAL option which gives you the desired numerical order without the (a) or (b) ...