topic Re: Data Not in Alphabetic or Numeric order in SAS Programming

Data Not in Alphabetic or Numeric order

edasdfasdfasdfa — Mon, 08 Feb 2021 16:28:52 GMT

So I'm reading this (below) in the documentation...

In BY-group processing, you can use data that is arranged in an order other than alphabetic or numeric, such as by calendar month or by category. To do this, use the NOTSORTED option in a BY statement when you use a SET statement. The NOTSORTED option in the BY statement tells SAS that the data is not in alphabetic or numeric order, but that it is arranged in groups by the values of the BY variable. You cannot use the NOTSORTED option with the MERGE statement, the UPDATE statement, or when the SET statement lists more than one data set.

I'm trying to wrap my head around the idea of when to use NOTSORTED.

So, is NOTSORTED literally only used when you haven't sorted your data-set so you are indicating that the values themselves dictate grouping not the order?

Re: Data Not in Alphabetic or Numeric order

PaigeMiller — Mon, 08 Feb 2021 16:53:09 GMT

When you are analyzing data via some PROC, and the data is grouped together by some variable (this is a requirement), but not sorted by that variable, then the BY statement with the NOTSORTED option works.

Example:

product       cost
red              10
red               20
red               11
blue              22
blue              19
green            7
green            8

Then you can use NOTSORTED in the BY statement in whatever PROC to get the results for RED and the results for BLUE and the results for GREEN. (Of course, you could sort it and then not worry about it, but if you have a large data set, NOTSORTED will work faster).

Re: Data Not in Alphabetic or Numeric order

FreelanceReinh — Mon, 08 Feb 2021 16:56:59 GMT

Hi @edasdfasdfasdfa,

Here's another example:

data have;
input a $ x y;
cards;
A 2 21
A 2 22
B 1 11
C 3 31
C 3 32
C 3 33
D 2 23
D 2 24
;

data want1;
set have;
by a;
if last.a;
run;

data want2;
set have;
by x notsorted;
if last.x;
run;

The results of the last two DATA steps are equal, but different (not only regarding sort order) from what you would get if you sorted dataset HAVE by X first and then created dataset WANT2 (with or without the notsorted option). Do you see why?

Re: Data Not in Alphabetic or Numeric order

Tom — Mon, 08 Feb 2021 17:05:24 GMT

You can use it when you want to treat the data as grouped by that variable, but the values are not either monotonically increasing nor monotonically decreasing.

One useful place to use this is when you have data with some type of STATUS variable that is recorded at various points in time. If you wanted to collapse to distinct contiguous intervals of the same status you could sort by the DATETIME variable but process it by the STATUS variable.

data intervals ;
  set have ;
  by id status notsorted ;
  if first.status then start=datetime;
  retain start ;
  if last.staus;
  stop=datetime;
  keep id status start stop;
  format start stop datetime19. ;
run;

Re: Data Not in Alphabetic or Numeric order

edasdfasdfasdfa — Mon, 08 Feb 2021 17:57:13 GMT

I guess the fact that the grouping is different for each DATA STEP?

Re: Data Not in Alphabetic or Numeric order

FreelanceReinh — Mon, 08 Feb 2021 18:40:15 GMT

With the original dataset HAVE the grouping in the "data want1" and "data want2" steps is the same because variables a and x change their values in exactly the same observations. After sorting HAVE by x, however, the two formerly separated BY groups with a='A' and a='D' form a single BY group with respect to variable x (four consecutive observations with x=2). Hence the "data want2" step would select only one observation (with y=24) from that new group, not two (y=22, y=24) as before.

So, if the original grouping is what you want, but no variable like a exists, then you're in a situation where the NOTSORTED option (applied to x) comes to the rescue.