Macros for SAS Application Developers
https://github.com/sasjs/core
Loading...
Searching...
No Matches
mp_sortinplace.sas
Go to the documentation of this file.
1/**
2 @file
3 @brief Sorts a SAS dataset in place, preserving constraints
4 @details Generally if a dataset contains indexes, then it is not necessary to
5 sort it before performing operations such as merges / joins etc.
6 That said, there are a few edge cases where it can be desirable:
7
8 @li To allow adjacent records to be viewed directly in the dataset
9 @li To apply compression, or to remove deleted records
10 @li To improve performance for specific queries
11
12 This macro will only work for BASE (V9) engine libraries. It works by
13 creating a copy of the dataset (without data, WITH constraints) in the same
14 library, appending a sorted view into it, and finally - renaming it.
15
16 Example usage:
17
18 proc sql;
19 create table work.example as
20 select * from sashelp.class;
21 alter table work.example
22 add constraint pk primary key(name);
23 %mp_sortinplace(work.example)
24
25 @param [in] libds The libref.datasetname that needs to be sorted
26
27 <h4> SAS Macros </h4>
28 @li mf_existds.sas
29 @li mf_getengine.sas
30 @li mf_getquotedstr.sas
31 @li mf_getuniquename.sas
32 @li mf_getvarlist.sas
33 @li mf_nobs.sas
34 @li mp_abort.sas
35 @li mp_getpk.sas
36
37 <h4> Related Macros </h4>
38 @li mp_sortinplace.test.sas
39
40 @version 9.2
41 @author Allan Bowe
42
43**/
44
45%macro mp_sortinplace(libds
46)/*/STORE SOURCE*/;
47
48%local lib ds tempds1 tempds2 tempvw sortkey;
49
50/* perform validations */
51%mp_abort(iftrue=(%sysfunc(countc(&libds,.)) ne 1)
52 ,mac=mp_sortinplace
53 ,msg=%str(LIBDS (&libds) should have LIBREF.DATASET format)
54)
55%mp_abort(iftrue=(%mf_existds(&libds)=0)
56 ,mac=mp_sortinplace
57 ,msg=%str(&libds does not exist)
58)
59
60%let lib=%scan(&libds,1,.);
61%let ds=%scan(&libds,2,.);
62%mp_abort(iftrue=(%mf_getengine(&lib) ne V9)
63 ,mac=mp_sortinplace
64 ,msg=%str(&lib is not a BASE engine library)
65)
66
67/* grab a copy of the constraints so we know what to sort by */
68%let tempds1=%mf_getuniquename(prefix=&sysmacroname);
69%mp_getpk(lib=&lib,ds=&ds,outds=work.&tempds1)
70
71%if %mf_nobs(work.&tempds1)=0 %then %do;
72 %put &sysmacroname: No PK found in &lib..&ds;
73 %put Sorting will not take place;
74 %return;
75%end;
76
77/* fallback sortkey is all fields */
78%let sortkey=%mf_getvarlist(&libds);
79
80/* overlay actual sort key if it exists */
81data _null_;
82 set work.&tempds1;
83 call symputx('sortkey',coalescec(pk_fields,symget('sortkey')));
84run;
85
86
87/* create empty copy, with ALL constraints, in the same library */
88%let tempds2=%mf_getuniquename(prefix=&sysmacroname);
89proc append base=&lib..&tempds2 data=&libds(obs=0);
90run;
91
92/* create sorted view */
93%let tempvw=%mf_getuniquename(prefix=&sysmacroname);
94proc sql;
95create view work.&tempvw as select * from &lib..&ds
96order by %mf_getquotedstr(&sortkey,quote=N);
97
98/* append sorted data */
99proc append base=&lib..&tempds2 data=work.&tempvw;
100run;
101
102/* do validations */
103%mp_abort(iftrue=(&syscc ne 0)
104 ,mac=mp_sortinplace
105 ,msg=%str(syscc=&syscc prior to replace operation)
106)
107%mp_abort(iftrue=(%mf_nobs(&lib..&tempds2) ne %mf_nobs(&lib..&ds))
108 ,mac=mp_sortinplace
109 ,msg=%str(new dataset has a different number of logical obs to the old)
110)
111
112/* drop old dataset */
113proc sql;
114drop table &lib..&ds;
115
116/* rename the new dataset */
117proc datasets library=&lib;
118 change &tempds2=&ds;
119run;
120
121
122%mend mp_sortinplace;