topic Re: proc sql summary table-order of categories in SAS Programming

proc sql summary table-order of categories

Ronein — Thu, 14 Oct 2021 08:52:21 GMT

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;

Re: proc sql summary table-order of categories

Kurt_Bremser — Thu, 14 Oct 2021 07:20:32 GMT

Use PROC FREQ with your format:

proc freq data=have;
format age agefmt.;
tables age;
run;

Re: proc sql summary table-order of categories

Kurt_Bremser — Thu, 14 Oct 2021 08:00:34 GMT

And to get your dataset, add the NOCUM and OUT= options:

proc freq data=have;
format age agefmt.;
tables age / nocum out=want;
run;

Re: proc sql summary table-order of categories

FreelanceReinh — Thu, 14 Oct 2021 08:26:38 GMT

Hello @Ronein,

It's true that PROC FREQ would be more suitable for this particular example, but let's assume that your real task calls for PROC SQL.

With a suitable ORDER BY clause you can specify virtually every conceivable sort order of records in PROC SQL. For example, insert this between the GROUP BY clause and the semicolon:

order by age net 'T', 1

This means: The output records are first sorted by the Boolean value (cf. Operators in PROC SQL expressions)

1 if age does not start with "T"
0 otherwise

(i.e., "TILL 18" is in group 0, the other age categories are in group 1 -- so "TILL 18" will be the first category in the output)

and, within these groups, records are sorted by the first item in the SELECT statement, i.e., by (calculated) age itself. This ensures the correct sort order of "18-19", "19-21" and "21+" because alphabetical order is appropriate for them.

Re: proc sql summary table-order of categories

Ronein — Thu, 14 Oct 2021 08:54:43 GMT

Thanks!!

Your solution is perfect

May you explain please what does it mean

order by age net 'T', 1



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 
		order by age net 'T', 1
;
QUIT;

Re: proc sql summary table-order of categories

Ronein — Thu, 14 Oct 2021 08:57:07 GMT

May you also show how to do it via proc tabulate and proc report and proc summary(calculate count and PCT from total and keep the order of categories as in the order in proc format)?

Re: proc sql summary table-order of categories

Ronein — Thu, 14 Oct 2021 09:00:50 GMT

What does it mean "net" ? Is it like "ne" (not)?

Re: proc sql summary table-order of categories

FreelanceReinh — Thu, 14 Oct 2021 10:11:32 GMT

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.

Re: proc sql summary table-order of categories

Kurt_Bremser — Thu, 14 Oct 2021 10:42:32 GMT

PROC REPORT:

proc report data=have;
column age id n percent;
define age / group format=agefmt. order=data;
define id / noprint;
define percent / computed format=percent7.2;
compute before;
all = n;
endcomp;
compute percent;
percent = n / all;
endcomp;
run;

PROC SUMMARY/MEANS:

proc means data=have n percent;
format age agefmt.;
class age / order=data;
run;

Re: proc sql summary table-order of categories

Kurt_Bremser — Thu, 14 Oct 2021 10:48:58 GMT

TABULATE:

proc tabulate data=have;
format age agefmt.;
class age;
tables age,(n pctn);
run;

Re: proc sql summary table-order of categories

PaigeMiller — Thu, 14 Oct 2021 11:30:15 GMT

@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) ...

Re: proc sql summary table-order of categories

Ronein — Fri, 15 Oct 2021 11:18:41 GMT

Is it no needed to write order=internal instead of order=data?
The ages in the data are not sorted from low to high...

Re: proc sql summary table-order of categories

Kurt_Bremser — Fri, 15 Oct 2021 13:23:33 GMT

You're right, I was fooled by your data ordered in a manner that made it work.

It's INTERNAL in PROC REPORT and UNFORMATTED in PROC MEANS.

Re: proc sql summary table-order of categories

PaigeMiller — Fri, 15 Oct 2021 13:38:17 GMT

INTERNAL is a valid alias for UNFORMATTED in PROC MEANS, and so at least in my mind, its easier to remember use INTERNAL for all PROCs where ORDER= is an option.

Re: proc sql summary table-order of categories

Kurt_Bremser — Fri, 15 Oct 2021 13:46:43 GMT

@PaigeMiller wrote:

INTERNAL is a valid alias for UNFORMATTED in PROC MEANS, and so at least in my mind, its easier to remember use INTERNAL for all PROCs where ORDER= is an option.

Re: proc sql summary table-order of categories

Ronein — Fri, 15 Oct 2021 19:05:53 GMT

Sorry, I didnt understand.

In order to sort by unformatted values need to do:

In PROC MEAN need to use data=Unformatted

in proc report need to use Data=internal

Am I correct???

Re: proc sql summary table-order of categories

PaigeMiller — Fri, 15 Oct 2021 20:25:07 GMT

@Ronein wrote:

Sorry, I didnt understand.

In order to sort by unformatted values need to do:

In PROC MEAN need to use data=Unformatted

in proc report need to use Data=internal

Am I correct???

PROC MEANS: you can use ORDER=INTERNAL or ORDER=UNFORMATTED, these are the same. In PROC REPORT, I have only ever used ORDER=INTERNAL. (And not DATA= as you typed)