35 %macro mp_guesspk(baseds
42 %local var vars vcnt i j k l tmpvar tmpds rows posspks ppkcnt;
43 %let vars=%mf_getvarlist(&baseds);
44 %let vcnt=%sysfunc(countw(&vars));
46 %
if &vcnt=0 %then %
do;
47 %put &sysmacroname: &baseds has no variables! Exiting.;
52 %let tmpvar=%mf_getuniquename();
54 create table _data_ as select
57 %let var=%scan(&vars,&i);
58 ,sum(case when &var is missing then 1 else 0 end) as &var
65 set &syslast end=last;
68 if _name_="&tmpvar" then call symputx('rows',col1,'l');
69 else if col1=0 then vars=catx(' ',vars,_name_);
70 if last then call symputx('posspks',vars,'l');
73 %let ppkcnt=%sysfunc(countw(&posspks));
74 %if &ppkcnt=0 %then %do;
75 %put &sysmacroname: &baseds has no non-missing variables! Exiting.;
79 proc sort data=&baseds(keep=&posspks) out=_data_ noduprec;
82 %local pkds; %let pkds=&syslast;
84 %if &rows > %mf_nobs(&pkds) %then %do;
85 %put &sysmacroname: &baseds has no combination of unique records! Exiting.;
91 create table _data_ as select
93 %let var=%scan(&posspks,&i);
94 count(distinct &var) as &var
95 %if &i<&ppkcnt %then ,;
101 proc sort; by descending col1;
105 data &outds(keep=pkguesses);
106 length pkguesses $5000 vars $5000;
107 set &syslast end=last;
109 vars=catx(' ',vars,_name_);
110 if col1=&rows then do;
114 if last then call symputx('posspks',vars,'l');
117 %if %mf_nobs(&outds) ge &max_guesses %then %do;
118 %put &sysmacroname: %mf_nobs(&outds) possible primary key values found;
122 %if &ppkcnt=1 %then %do;
123 %put &sysmacroname: No more PK guess possible;
128 %let tmpds=%mf_getuniquename();
131 %let lev1=%scan(&posspks,&i);
133 %let lev2=%scan(&posspks,&j);
134 %if &lev1 ne &lev2 %then %do;
136 proc sort data=&pkds(keep=&lev1 &lev2) out=&tmpds noduprec;
139 %if %mf_nobs(&tmpds)=&rows %then %do;
141 insert into &outds values("&lev1 &lev2");
142 %if %mf_nobs(&outds) ge &max_guesses %then %do;
143 %put &sysmacroname: Max PKs reached at Level 2 for &baseds;
151 %if &ppkcnt=2 %then %do;
152 %put &sysmacroname: No more PK guess possible;
159 %let lev1=%scan(&posspks,&i);
161 %let lev2=%scan(&posspks,&j);
162 %if &lev1 ne &lev2 %then %do k=3 %to &ppkcnt;
163 %let lev3=%scan(&posspks,&k);
164 %if &lev1 ne &lev3 and &lev2 ne &lev3 %then %do;
166 proc sort data=&pkds(keep=&lev1 &lev2 &lev3) out=&tmpds noduprec;
169 %if %mf_nobs(&tmpds)=&rows %then %do;
171 insert into &outds values("&lev1 &lev2 &lev3");
172 %if %mf_nobs(&outds) ge &max_guesses %then %do;
173 %put &sysmacroname: Max PKs reached at Level 3 for &baseds;
182 %if &ppkcnt=3 %then %do;
183 %put &sysmacroname: No more PK guess possible;
190 %let lev1=%scan(&posspks,&i);
192 %let lev2=%scan(&posspks,&j);
193 %if &lev1 ne &lev2 %then %do k=3 %to &ppkcnt;
194 %let lev3=%scan(&posspks,&k);
195 %if &lev1 ne &lev3 and &lev2 ne &lev3 %then %do l=4 %to &ppkcnt;
196 %let lev4=%scan(&posspks,&l);
197 %if &lev1 ne &lev4 and &lev2 ne &lev4 and &lev3 ne &lev4 %then %do;
199 proc sort data=&pkds(keep=&lev1 &lev2 &lev3 &lev4) out=&tmpds noduprec;
202 %if %mf_nobs(&tmpds)=&rows %then %do;
204 insert into &outds values("&lev1 &lev2 &lev3 &lev4");
205 %if %mf_nobs(&outds) ge &max_guesses %then %do;
206 %put &sysmacroname: Max PKs reached at Level 4 for &baseds;
216 %if &ppkcnt=4 %then %do;
217 %put &sysmacroname: No more PK guess possible;
224 %let lev1=%scan(&posspks,&i);
226 %let lev2=%scan(&posspks,&j);
227 %if &lev1 ne &lev2 %then %do k=3 %to &ppkcnt;
228 %let lev3=%scan(&posspks,&k);
229 %if &lev1 ne &lev3 and &lev2 ne &lev3 %then %do l=4 %to &ppkcnt;
230 %let lev4=%scan(&posspks,&l);
231 %if &lev1 ne &lev4 and &lev2 ne &lev4 and &lev3 ne &lev4 %then
233 %let lev5=%scan(&posspks,&m);
234 %if &lev1 ne &lev5 & &lev2 ne &lev5 & &lev3 ne &lev5 & &lev4 ne &lev5 %then %do;
236 proc sort data=&pkds(keep=&lev1 &lev2 &lev3 &lev4 &lev5) out=&tmpds noduprec;
239 %if %mf_nobs(&tmpds)=&rows %then %do;
241 insert into &outds values("&lev1 &lev2 &lev3 &lev4 &lev5");
242 %if %mf_nobs(&outds) ge &max_guesses %then %do;
243 %put &sysmacroname: Max PKs reached at Level 5 for &baseds;
254 %if &ppkcnt=5 %then %do;
255 %put &sysmacroname: No more PK guess possible;
262 %let lev1=%scan(&posspks,&i);
264 %let lev2=%scan(&posspks,&j);
265 %if &lev1 ne &lev2 %then %do k=3 %to &ppkcnt;
266 %let lev3=%scan(&posspks,&k);
267 %if &lev1 ne &lev3 and &lev2 ne &lev3 %then %do l=4 %to &ppkcnt;
268 %let lev4=%scan(&posspks,&l);
269 %if &lev1 ne &lev4 and &lev2 ne &lev4 and &lev3 ne &lev4 %then
271 %let lev5=%scan(&posspks,&m);
272 %if &lev1 ne &lev5 & &lev2 ne &lev5 & &lev3 ne &lev5 & &lev4 ne &lev5 %then
274 %let lev6=%scan(&posspks,&n);
275 %if &lev1 ne &lev6 & &lev2 ne &lev6 & &lev3 ne &lev6
276 & &lev4 ne &lev6 & &lev5 ne &lev6 %then
279 proc sort data=&pkds(keep=&lev1 &lev2 &lev3 &lev4 &lev5 &lev6)
283 %if %mf_nobs(&tmpds)=&rows %then %do;
285 insert into &outds values("&lev1 &lev2 &lev3 &lev4 &lev5 &lev6");
286 %if %mf_nobs(&outds) ge &max_guesses %then %do;
287 %put &sysmacroname: Max PKs reached at Level 6 for &baseds;
299 %if &ppkcnt=6 %then %do;
300 %put &sysmacroname: No more PK guess possible;