Macros for SAS Application Developers
https://github.com/sasjs/core
Loading...
Searching...
No Matches
mp_guesspk.sas
Go to the documentation of this file.
1/**
2 @file
3 @brief Guess the primary key of a table
4 @details Tries to guess the primary key of a table based on the following
5 logic:
6
7 * Columns with nulls are ignored
8 * Return only column combinations that provide unique results
9 * Start from one column, then move out to composite keys of 2 to 6 columns
10
11 The library of the target should be assigned before using this macro.
12
13 Usage:
14
15 filename mc url
16 "https://raw.githubusercontent.com/sasjs/core/main/all.sas";
17 %inc mc;
18 %mp_guesspk(sashelp.class,outds=classpks)
19
20 @param [in] baseds The dataset to analyse
21 @param [out] outds= (mp_guesspk) Output dataset to contain the possible PKs
22 @param [in] max_guesses= (3) The total number of possible primary keys to
23 generate. A table may have multiple (unlikely) PKs, so no need to list them
24 all.
25 @param [in] min_rows= (5) The minimum number of rows a table should have in
26 order to try and guess the PK.
27 @param [in] ignore_cols (0) Space seperated list of columns which you are
28 sure are not part of the primary key (helps to avoid false positives)
29 @param [in] mdebug= Set to 1 to enable DEBUG messages and preserve outputs
30
31 <h4> SAS Macros </h4>
32 @li mf_getvarlist.sas
33 @li mf_getuniquename.sas
34 @li mf_wordsinstr1butnotstr2.sas
35 @li mf_nobs.sas
36
37 <h4> Related Macros </h4>
38 @li mp_getpk.sas
39
40 @version 9.3
41 @author Allan Bowe
42
43 @sasjslint {"maxLineLength": 90}
44**/
45
46%macro mp_guesspk(baseds
47 ,outds=mp_guesspk
48 ,max_guesses=3
49 ,min_rows=5
50 ,ignore_cols=0
51 ,mdebug=0
52)/*/STORE SOURCE*/;
53%local dbg;
54%if &mdebug=1 %then %do;
55 %put &sysmacroname entry vars:;
56 %put _local_;
57%end;
58%else %let dbg=*;
59
60/* declare local vars */
61%local var vars vcnt i j k l tmpvar tmpds rows posspks ppkcnt;
62%let vars=%upcase(%mf_getvarlist(&baseds));
63%let vars=%mf_wordsInStr1ButNotStr2(str1=&vars,str2=%upcase(&ignore_cols));
64%let vcnt=%sysfunc(countw(&vars));
65
66%if &vcnt=0 %then %do;
67 %put &sysmacroname: &baseds has no variables! Exiting.;
68 %return;
69%end;
70
71/* get null count and row count */
72%let tmpvar=%mf_getuniquename();
73proc sql noprint;
74create table _data_ as select
75 count(*) as &tmpvar
76%do i=1 %to &vcnt;
77 %let var=%scan(&vars,&i);
78 ,sum(case when &var is missing then 1 else 0 end) as &var
79%end;
80 from &baseds;
81
82/* transpose table and scan for not null cols */
83proc transpose;
84data _null_;
85 set &syslast end=last;
86 length vars $32767;
87 retain vars ;
88 if _name_="&tmpvar" then call symputx('rows',col1,'l');
89 else if col1=0 then vars=catx(' ',vars,_name_);
90 if last then call symputx('posspks',vars,'l');
91run;
92
93%let ppkcnt=%sysfunc(countw(&posspks));
94%if &ppkcnt=0 %then %do;
95 %put &sysmacroname: &baseds has no non-missing variables! Exiting.;
96 %return;
97%end;
98
99proc sort data=&baseds(keep=&posspks) out=_data_ noduprec;
100 by _all_;
101run;
102%local pkds; %let pkds=&syslast;
103
104%if &rows > %mf_nobs(&pkds) %then %do;
105 %put &sysmacroname: &baseds has no combination of unique records! Exiting.;
106 %return;
107%end;
108
109/* now check cardinality */
110proc sql noprint;
111create table _data_ as select
112%do i=1 %to &ppkcnt;
113 %let var=%scan(&posspks,&i);
114 count(distinct &var) as &var
115 %if &i<&ppkcnt %then ,;
116%end;
117 from &pkds;
118
119/* transpose and sort by cardinality */
120proc transpose;
121proc sort; by descending col1;
122run;
123
124/* create initial PK list and re-order posspks list */
125data &outds(keep=pkguesses);
126 length pkguesses $5000 vars $5000;
127 set &syslast end=last;
128 retain vars ;
129 vars=catx(' ',vars,_name_);
130 if col1=&rows then do;
131 pkguesses=_name_;
132 output;
133 end;
134 if last then call symputx('posspks',vars,'l');
135run;
136
137%if %mf_nobs(&outds) ge &max_guesses %then %do;
138 %put &sysmacroname: %mf_nobs(&outds) possible primary key values found;
139 %return;
140%end;
141
142%if &ppkcnt=1 %then %do;
143 %put &sysmacroname: No more PK guess possible;
144 %return;
145%end;
146
147/* begin scanning for uniques on pairs of PKs */
148%let tmpds=%mf_getuniquename();
149%local lev1 lev2;
150%do i=1 %to &ppkcnt;
151 %let lev1=%scan(&posspks,&i);
152 %do j=2 %to &ppkcnt;
153 %let lev2=%scan(&posspks,&j);
154 %if &lev1 ne &lev2 %then %do;
155 /* check for two level uniqueness */
156 proc sort data=&pkds(keep=&lev1 &lev2) out=&tmpds noduprec;
157 by _all_;
158 run;
159 %if %mf_nobs(&tmpds)=&rows %then %do;
160 proc sql;
161 insert into &outds values("&lev1 &lev2");
162 %if %mf_nobs(&outds) ge &max_guesses %then %do;
163 %put &sysmacroname: Max PKs reached at Level 2 for &baseds;
164 %goto exit;
165 %end;
166 %end;
167 %end;
168 %end;
169%end;
170
171%if &ppkcnt=2 %then %do;
172 %put &sysmacroname: No more PK guess possible;
173 %goto exit;
174%end;
175
176/* begin scanning for uniques on PK triplets */
177%local lev3;
178%do i=1 %to &ppkcnt;
179 %let lev1=%scan(&posspks,&i);
180 %do j=2 %to &ppkcnt;
181 %let lev2=%scan(&posspks,&j);
182 %if &lev1 ne &lev2 %then %do k=3 %to &ppkcnt;
183 %let lev3=%scan(&posspks,&k);
184 %if &lev1 ne &lev3 and &lev2 ne &lev3 %then %do;
185 /* check for three level uniqueness */
186 proc sort data=&pkds(keep=&lev1 &lev2 &lev3) out=&tmpds noduprec;
187 by _all_;
188 run;
189 %if %mf_nobs(&tmpds)=&rows %then %do;
190 proc sql;
191 insert into &outds values("&lev1 &lev2 &lev3");
192 %if %mf_nobs(&outds) ge &max_guesses %then %do;
193 %put &sysmacroname: Max PKs reached at Level 3 for &baseds;
194 %goto exit;
195 %end;
196 %end;
197 %end;
198 %end;
199 %end;
200%end;
201
202%if &ppkcnt=3 %then %do;
203 %put &sysmacroname: No more PK guess possible;
204 %goto exit;
205%end;
206
207/* scan for uniques on up to 4 PK fields */
208%local lev4;
209%do i=1 %to &ppkcnt;
210 %let lev1=%scan(&posspks,&i);
211 %do j=2 %to &ppkcnt;
212 %let lev2=%scan(&posspks,&j);
213 %if &lev1 ne &lev2 %then %do k=3 %to &ppkcnt;
214 %let lev3=%scan(&posspks,&k);
215 %if &lev1 ne &lev3 and &lev2 ne &lev3 %then %do l=4 %to &ppkcnt;
216 %let lev4=%scan(&posspks,&l);
217 %if &lev1 ne &lev4 and &lev2 ne &lev4 and &lev3 ne &lev4 %then %do;
218 /* check for four level uniqueness */
219 proc sort data=&pkds(keep=&lev1 &lev2 &lev3 &lev4)
220 out=&tmpds noduprec;
221 by _all_;
222 run;
223 %if %mf_nobs(&tmpds)=&rows %then %do;
224 proc sql;
225 insert into &outds values("&lev1 &lev2 &lev3 &lev4");
226 %if %mf_nobs(&outds) ge &max_guesses %then %do;
227 %put &sysmacroname: Max PKs reached at Level 4 for &baseds;
228 %goto exit;
229 %end;
230 %end;
231 %end;
232 %end;
233 %end;
234 %end;
235%end;
236
237%if &ppkcnt=4 %then %do;
238 %put &sysmacroname: No more PK guess possible;
239 %goto exit;
240%end;
241
242/* scan for uniques on up to 4 PK fields */
243%local lev5 m;
244%do i=1 %to &ppkcnt;
245 %let lev1=%scan(&posspks,&i);
246 %do j=2 %to &ppkcnt;
247 %let lev2=%scan(&posspks,&j);
248 %if &lev1 ne &lev2 %then %do k=3 %to &ppkcnt;
249 %let lev3=%scan(&posspks,&k);
250 %if &lev1 ne &lev3 and &lev2 ne &lev3 %then %do l=4 %to &ppkcnt;
251 %let lev4=%scan(&posspks,&l);
252 %if &lev1 ne &lev4 and &lev2 ne &lev4 and &lev3 ne &lev4 %then
253 %do m=5 %to &ppkcnt;
254 %let lev5=%scan(&posspks,&m);
255 %if &lev1 ne &lev5 & &lev2 ne &lev5 & &lev3 ne &lev5 & &lev4 ne &lev5 %then %do;
256 /* check for four level uniqueness */
257 proc sort data=&pkds(keep=&lev1 &lev2 &lev3 &lev4 &lev5)
258 out=&tmpds noduprec;
259 by _all_;
260 run;
261 %if %mf_nobs(&tmpds)=&rows %then %do;
262 proc sql;
263 insert into &outds values("&lev1 &lev2 &lev3 &lev4 &lev5");
264 %if %mf_nobs(&outds) ge &max_guesses %then %do;
265 %put &sysmacroname: Max PKs reached at Level 5 for &baseds;
266 %goto exit;
267 %end;
268 %end;
269 %end;
270 %end;
271 %end;
272 %end;
273 %end;
274%end;
275
276%if &ppkcnt=5 %then %do;
277 %put &sysmacroname: No more PK guess possible;
278 %goto exit;
279%end;
280
281/* scan for uniques on up to 4 PK fields */
282%local lev6 n;
283%do i=1 %to &ppkcnt;
284 %let lev1=%scan(&posspks,&i);
285 %do j=2 %to &ppkcnt;
286 %let lev2=%scan(&posspks,&j);
287 %if &lev1 ne &lev2 %then %do k=3 %to &ppkcnt;
288 %let lev3=%scan(&posspks,&k);
289 %if &lev1 ne &lev3 and &lev2 ne &lev3 %then %do l=4 %to &ppkcnt;
290 %let lev4=%scan(&posspks,&l);
291 %if &lev1 ne &lev4 and &lev2 ne &lev4 and &lev3 ne &lev4 %then
292 %do m=5 %to &ppkcnt;
293 %let lev5=%scan(&posspks,&m);
294 %if &lev1 ne &lev5 & &lev2 ne &lev5 & &lev3 ne &lev5 & &lev4 ne &lev5
295 %then %do n=6 %to &ppkcnt;
296 %let lev6=%scan(&posspks,&n);
297 %if &lev1 ne &lev6 & &lev2 ne &lev6 & &lev3 ne &lev6
298 & &lev4 ne &lev6 & &lev5 ne &lev6 %then
299 %do;
300 /* check for four level uniqueness */
301 proc sort data=&pkds(keep=&lev1 &lev2 &lev3 &lev4 &lev5 &lev6)
302 out=&tmpds noduprec;
303 by _all_;
304 run;
305 %if %mf_nobs(&tmpds)=&rows %then %do;
306 proc sql;
307 insert into &outds
308 values("&lev1 &lev2 &lev3 &lev4 &lev5 &lev6");
309 %if %mf_nobs(&outds) ge &max_guesses %then %do;
310 %put &sysmacroname: Max PKs reached at Level 6 for &baseds;
311 %goto exit;
312 %end;
313 %end;
314 %end;
315 %end;
316 %end;
317 %end;
318 %end;
319 %end;
320%end;
321
322%if &ppkcnt=6 %then %do;
323 %put &sysmacroname: No more PK guess possible;
324 %goto exit;
325%end;
326
327%exit:
328%if &mdebug=0 %then %do;
329 proc sql;
330 drop table &tmpds;
331%end;
332
333%mend mp_guesspk;