46%macro mp_guesspk(baseds
54%
if &mdebug=1 %then %
do;
55 %put &sysmacroname entry 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));
67 %put &sysmacroname: &baseds has no variables! Exiting.;
72%let tmpvar=%mf_getuniquename();
74create table _data_ as select
77 %let var=%scan(&vars,&i);
78 ,sum(case when &var is missing then 1 else 0 end) as &var
85 set &syslast end=last;
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');
93%let ppkcnt=%sysfunc(countw(&posspks));
94%if &ppkcnt=0 %then %do;
95 %put &sysmacroname: &baseds has no non-missing variables! Exiting.;
99proc sort data=&baseds(keep=&posspks) out=_data_ noduprec;
102%local pkds; %let pkds=&syslast;
104%if &rows > %mf_nobs(&pkds) %then %do;
105 %put &sysmacroname: &baseds has no combination of unique records! Exiting.;
111create table _data_ as select
113 %let var=%scan(&posspks,&i);
114 count(distinct &var) as &var
115 %if &i<&ppkcnt %then ,;
121proc sort; by descending col1;
125data &outds(keep=pkguesses);
126 length pkguesses $5000 vars $5000;
127 set &syslast end=last;
129 vars=catx(
' ',vars,_name_);
130 if col1=&rows then do;
134 if last then call symputx('posspks',vars,
'l');
137%if %mf_nobs(&outds) ge &max_guesses %then %do;
138 %put &sysmacroname: %mf_nobs(&outds) possible primary key values found;
142%if &ppkcnt=1 %then %do;
143 %put &sysmacroname: No more PK guess possible;
148%let tmpds=%mf_getuniquename();
151 %let lev1=%scan(&posspks,&i);
153 %let lev2=%scan(&posspks,&j);
154 %if &lev1 ne &lev2 %then %do;
156 proc sort data=&pkds(keep=&lev1 &lev2) out=&tmpds noduprec;
159 %if %mf_nobs(&tmpds)=&rows %then %do;
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;
171%if &ppkcnt=2 %then %do;
172 %put &sysmacroname: No more PK guess possible;
179 %let lev1=%scan(&posspks,&i);
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;
186 proc sort data=&pkds(keep=&lev1 &lev2 &lev3) out=&tmpds noduprec;
189 %if %mf_nobs(&tmpds)=&rows %then %do;
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;
202%if &ppkcnt=3 %then %do;
203 %put &sysmacroname: No more PK guess possible;
210 %let lev1=%scan(&posspks,&i);
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;
219 proc sort data=&pkds(keep=&lev1 &lev2 &lev3 &lev4)
223 %if %mf_nobs(&tmpds)=&rows %then %do;
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;
237%if &ppkcnt=4 %then %do;
238 %put &sysmacroname: No more PK guess possible;
245 %let lev1=%scan(&posspks,&i);
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
254 %let lev5=%scan(&posspks,&m);
255 %if &lev1 ne &lev5 & &lev2 ne &lev5 & &lev3 ne &lev5 & &lev4 ne &lev5 %then %do;
257 proc sort data=&pkds(keep=&lev1 &lev2 &lev3 &lev4 &lev5)
261 %if %mf_nobs(&tmpds)=&rows %then %do;
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;
276%if &ppkcnt=5 %then %do;
277 %put &sysmacroname: No more PK guess possible;
284 %let lev1=%scan(&posspks,&i);
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
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
301 proc sort data=&pkds(keep=&lev1 &lev2 &lev3 &lev4 &lev5 &lev6)
305 %if %mf_nobs(&tmpds)=&rows %then %do;
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;
322%if &ppkcnt=6 %then %do;
323 %put &sysmacroname: No more PK guess possible;
328%if &mdebug=0 %then %do;