SAS: unique identifier for multiple columns with same values

[learsaas]

/*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;

[novinosrin]

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

[novinosrin]

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

[novinosrin]

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;