Accepted Solutions
So nope. I stand by my original answer, you cannot assume that a data set is sorted. The sort flag does not propogate across. There may be an implicit sort, but you're taking a risk there when SAS is not sorting the data. I will add this may behave differently if you're working on DBs because SQL by definition doesn't maintain row orders.
BY does check for the implicit sort and won't error out if it's there so likely that's what you're seeing and if its good enough for you on this system you're good. If you change things you may have to re-think this, I would probably add a check to ensure the data is sorted as needed or at least if it errors out it will explain why.
*implicitly sorted data set;
data have;
input ID Age;
cards;
1 5
2 8
3 13
4 14
5 3
6 34
7 6
8 3
9 3
10 2
;;;;
*check by/where without sort;
data want1 (label="Implicit Sort, BY and Where");
set have;
by id;
where id in (1, 2, 3);
run;
*explicitly sort data set now;
proc sort data=have out=have2 (label="Sorted input data");
by id;
run;
data want2 (label="explicit Sort, Only Where");
set have2;
where id in (1, 2, 3);
run;
data want3 (label="explicit sort, By and Where");
set have2;
by id;
where id in (1, 2,3 );
run;
data want3 (label="Explicit sort, copy only with BY");
set have2;
by id;
run;
data results;
set sashelp.vtable;
where libname='WORK' and (%Upcase(memname) like '%WANT%' or %Upcase(memname) like '%HAVE%') ;
keep libname memname memlabel sort:;
run;
The sorting of the original data is correct by PERMNO and DATE. Here I attach the code and the screenshots.
rsubmit;
data msf;
set crsp.msf;
by permno date;/*<-this works correctly*/
run;
data msf1;/*<-the resulting msf1 is not sorted*/
set crsp.msf;
where date<"1jan1930"d;
run;
data msf2;/*<-the resulting msf2 is sorted*/
set crsp.msf;
where date<"1jan1930"d;
by permno date;
run;
endrsubmit;The code above creates three data sets—MSF, MSF1, and MSF2. The BY in the first DATA has no problem.
The only difference between MSF1 and MSF2 is the BY statement in the third DATA.
3 rsubmit;
NOTE: Remote submit to WRDS commencing.
1 data msf;
2 set crsp.msf;
3 by permno date;/*<-this works correctly*/
4 run;
NOTE: There were 4509846 observations read from the data set CRSP.MSF.
NOTE: The data set WORK.MSF has 4509846 observations and 21 variables.
NOTE: DATA statement used (Total process time):
real time 1.61 seconds
cpu time 1.52 seconds
5 data msf1;/*<-the resulting msf1 is not sorted*/
6 set crsp.msf;
7 where date<"1jan1930"d;
8 run;
NOTE: There were 29968 observations read from the data set CRSP.MSF.
WHERE date<'01JAN1930'D;
NOTE: The data set WORK.MSF1 has 29968 observations and 21 variables.
NOTE: DATA statement used (Total process time):
real time 0.68 seconds
cpu time 0.43 seconds
9 data msf2;/*<-the resulting msf2 is sorted*/
10 set crsp.msf;
11 where date<"1jan1930"d;
12 by permno date;
13 run;
NOTE: There were 29968 observations read from the data set CRSP.MSF.
WHERE date<'01JAN1930'D;
NOTE: The data set WORK.MSF2 has 29968 observations and 21 variables.
NOTE: DATA statement used (Total process time):
real time 1.00 seconds
cpu time 0.44 seconds
NOTE: Remote submit to WRDS complete.The following is the resulting (not original) MSF. The observations are sorted correctly by PERMNO and DATE.
The following is MSF1 without BY. The PERMNO-DATE sorting disappears.
The following is MSF2 with BY. The PERMNO-DATE sorting is correct.
It seems SAS does not access to the original data sequentially if not BY.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Click image to register for webinar
Classroom Training Available!
Select SAS Training centers are offering in-person courses. View upcoming courses for:
-
1098 views
-
3 likes
-
-
