/*method 1:SQL*/
%macro getResult;
%local i j NOBS1 NOBS2;
proc sql noprint;
create table test_left as
select *
from test
;
create table test_result as
select *,'' as UID length=20
from test_left
where 1=2
;
quit;
%let i=1;
%do %until(%left(&NOBS1) eq 0);
%let NOBS1=0;
proc sql noprint;
select count(*) into :NOBS1 from test_left;
quit;
%if %left(&NOBS1) gt 0 %then %do;
%let j=1;
%do %until(%left(&NOBS2) eq 0);
%if &j eq 1 %then %do;
proc sql noprint;
create table test_id as
select distinct cust
from test_left(firstobs=1 obs=1)
;
quit;
%end;
%else %do;
proc sql noprint;
create table test_id as
select distinct cust
from test_left
where dev in (select distinct dev from test_temp)
;
quit;
%end;
proc sql noprint;
create table test_temp as
select *
from test_left
where cust in (select distinct cust from test_id)
;
create table test_left as
select *
from test_left
where cust not in (select distinct cust from test_id)
;
quit;
%let NOBS2=0;
proc sql noprint;
select count(*) into :NOBS2 from test_temp;
quit;
%if %left(&NOBS2) gt 0 %then %do;
proc sql noprint;
insert into test_result
select *,cats('UID',"%left(&i)") as UID length=20
from test_temp
;
quit;
%end;
%let j=%eval(&j+1);
%end;
%end;
%let i=%eval(&i+1);
%end;
proc sql noprint;
drop table test_left;
drop table test_temp;
drop table test_id;
quit;
%mend;
%getResult;
/*method 2*/
%macro getResult;
%local i j NOBS1 NOBS2;
data test_left;
set test;
run;
data test_result;
set test_left;
attrib UID length=$20;
stop;
run;
%let i=1;
%do %until(%left(&NOBS1 eq 0));
%let NOBS1=0;
proc sql noprint;
select count(*) into :NOBS1 from test_left;
quit;
%if %left(&NOBS1) gt 0 %then %do;
%let j=1;
%do %until(%left(&NOBS2 eq 0));
%if &j eq 1 %then %do;
data test_id;
set test_left(firstobs=1 obs=1);
run;
%end;
%else %do;
proc sql noprint;
create table test_id as
select distinct cust
from test_left
where dev in (select distinct dev from test_temp)
;
quit;
%end;
data test_left test_temp;
set test_left;
Flag=0;
do i=1 to ncount;
set test_id(keep=cust rename=(cust=cust_id)) nobs=ncount point=i;
if cust_id=cust then do;Flag=1;leave;end;
end;
if flag=1 then output test_temp;else output test_left;
drop flag cust_id;
run;
%let NOBS2=0;
proc sql noprint;
select count(*) into :NOBS2 from test_temp;
quit;
%if %left(&NOBS2) gt 0 %then %do;
data temp;
set test_temp;
UID='UID'||"%left(&i)";
run;
proc append base=test_result data=temp force;
run;
%end;
%let j=%eval(&j+1);
%end;
%end;
%let i=%eval(&i+1);
%end;
proc delete data=test_left;run;
proc delete data=test_temp;run;
proc delete data=test_id;run;
proc delete data=temp;run;
%mend;
%getResult;
Hi @kilo_foxtrot This is close to applying brute force. Try and let me know.
data have;
length cust dev $5;
input cust $ dev $;
datalines;
a 1
a 2
a 7
b 2
b 3
c 3
d 4
e 5
h 2
x 4
x 5
y 7
y 5
;
run;
dm log 'clear';
/*Intial look up*/
data _null_;
if _n_=1 then do;
length uid __dev __cust $5;
if 0 then set have(rename=(dev=_dev cust=_cust));
declare hash H (multidata:'yes') ;
h.definekey ("__cust") ;
h.definedata ("__cust","__dev","uid") ;
h.definedone () ;
declare hash H1 (dataset:'have(rename=(dev=_dev cust=_cust))', multidata:'yes') ;
h1.definekey ("_dev") ;
h1.definedata ("_cust","_dev") ;
h1.definedone () ;
declare hiter hi('h');
declare hiter hh('h1');
call missing(__cust,__dev);
end;
array t(999) $5;
n=0;
do until(last.cust);
set have end=l;
by cust;
if first.cust then
do;
if h.find(key:cust) = 0 then f1=1;
do while(hi.next()=0);
if dev=__dev then do; f2=1;leave;end;
end;
if sum(f1, f2)=. then do;c+1;uid =cats( 'UID', put(c,8. -l)); end;
end;
do rc1=h1.find(key:dev) by 0 while(rc1=0);
rc=h.add(key:_cust,data:_cust,data: _dev,data:uid);
if _cust ne cust then do;
n+1;
t(n)=_cust;
end;
rc1=h1.find_next();
end;
end;
do i=1 to dim(t);
if not missing(t(i)) then
do while(hh.next()=0);
if t(i)=_cust then rc=h.add(key:_cust,data:_cust,data: _dev,data:uid);
do rc1=h1.find(key:_dev) by 0 while(rc1=0);
rc=h.add(key:_cust,data:_cust,data: _dev,data:uid);
rc1=h1.find_next();
end;
end;
end;
if l then h.output(dataset:'w');
run;
/*Final Want */
proc sort data=w out=want nodupkey;
by uid __cust __dev;
run;
There is still a glitch. Ignore this post .Another garbage.hmm let me try again
HI @kilo_foxtrot I spent sometime talking to my mate and researching online. It seems networking problems often tend to have routes(paths) that can be infinite as in our example, the more I try the link keeps going, on and on and on. Sure this can only go so far because the dataset isn't infinite but i think grouping one that is vast and expanded with duplicates needs a different approach.
Anyways, Can you provide me some logical/mathematical insight without referring to technology/functionality to guage how the loop stops from your experience/knowledge.
Ignore all. Understood all but my brain didn't function well. My bad
Hi @kilo_foxtrot Dec,17,2018 attempt
data have;
length cust dev $5;
input cust $ dev $;
datalines;
a 1
a 2
a 51
b 2
b 3
c 3
d 4
e 5
h 2
x 4
x 5
y 7
y 5
z 8
k 6
l 9
l 3
j 51
j 9
j 1
m 11
n 13
;
run;
proc sort data=have;
by cust dev;
run;
dm log 'clear';
data _null_;
if _n_=1 then do;
if 0 then set have(rename=(dev=_dev cust=_cust));
length __dev UID __cust $5;
/*H initial full load*/
dcl hash H (dataset:'have(rename=(dev=_dev cust=_cust))',multidata:'y') ;
h.definekey ("_dev") ;
h.definedata ("_cust", "_dev") ;
h.definedone () ;
dcl hiter hh('h');
/*H1 Look up and load part by part load proceeding to our need*/
dcl hash H1 (multidata:'y',ordered:'y') ;
h1.definekey ("__cust") ;
h1.definedata ("__cust", "__dev","UID") ;
h1.definedone () ;
dcl hiter hh1('h1');
call missing(__dev,__cust);
end;
array t(999) $5;
array j(999) $5;
do until(last.cust);
set have end=l;
by cust;
if first.cust then
do;
if h1.find(key:cust)= 0 then f=1;
if not f then do; c+1; uid= cats( 'UID', put(c,8. -l));end;
end;
if not f then
do;
/*Look from dev to cust and collect cust residuals in array*/
do rc1=h.find(key:dev) by 0 while(rc1=0);
rc=h1.add(key:_cust,data:_cust,data:_dev,data:uid);
/*collect cust residuals in array that's not part of cust but part of device*/
if _cust ne cust then do;n+1;t(n)=_cust;end;
rc1=h.find_next();
end;
end;
end;
n=0;
if not f then
do;
/*Residual look up-start with cust and iterate with dev*cust recursively until all check complete*/
do until(sum(cmiss(of t(*)),cmiss(of j(*)))=dim(t)*2);
do i=1 to dim(t);
if not missing(t(i)) then
/*iterate the full load h using hh*/
do while(hh.next()=0);
if t(i)=_cust then
do;
/*Check if residual cust's* dev is not in part load hash,if not then fetch*/
k=.;
do while(hh1.next()=0);
if __dev = _dev then do; k=1;leave;end;
end;
if not k then do;n1+1;j(n1)=_dev;end;
rc=h1.add(key:_cust,data:t(i),data:_dev,data:uid);
end;
end;
end;
call missing(of t(*));n1=0;
do i=1 to dim(j);
if not missing(j(i)) then
do rc1=h.find(key:j(i)) by 0 while(rc1=0);
/*Check if residual dev's *cust is not in part load hash,if not then fetch*/
if h1.check(key:_cust) ne 0 then do;n+1;t(n)=_cust;end;
rc=h1.add(key:_cust,data:_cust,data:_dev,data:uid);
rc1=h.find_next();
end;
end;
call missing(of j(*));n=0;
end;
end;
if l then h1.output(dataset:'want');
run;
proc sort data=want out=final_want nodupkey ;
by uid __cust __dev ;
run;
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
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.
Ready to level-up your skills? Choose your own adventure.