topic Re: SAS: unique identifier for multiple columns with same values in SAS Programming

SAS: unique identifier for multiple columns with same values

kilo_foxtrot — Mon, 10 Dec 2018 14:29:47 GMT

Take the example code below containing customers and their devices:

data test;
    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
    ;
run;

I'm trying to group ALL related individuals by customer and device into unique IDs. For example, Customer A is related to Customer B through Device 2, and Customer B is related to Customer C through Device 3. Because A is related to B and B is related to C, these should have the same UID. If you think of customer as the 'parent' and device as the 'child' in node structure, all linked children and parents should be in the same UID. Any breaks should be separate UIDs.

Below is the desired output:

Customer|Device|UID
    a       1   UID1
    a       2   UID1
    a       7   UID1
    b       2   UID1
    b       3   UID1
    c       3   UID1
    h       2   UID1
    d       4   UID2
    x	    4	UID2
    e       5   UID3

I have tried transposing by Customer and doing a 'cascade' retain on customer and device but the code gets pretty hairy. Also, I do not have access to PROC BOM which I've read may help my case. Does anyone have any ideas on how this might be achieved?

Re: SAS: unique identifier for multiple columns with same values

Kurt_Bremser — Mon, 10 Dec 2018 15:59:36 GMT

You will need to keep two hash objects to keep track of already assigned uid's:

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
    ;
run;

data want;
set have end=eof;
length uid $4;
retain counter 0;
if _n_ = 1
then do;
  declare hash devices();
  devices.definedata("uid",'dev');
  devices.definekey("dev");
  devices.definedone();
  declare hash customers();
  customers.definedata("uid",'cust');
  customers.definekey("cust");
  customers.definedone();
  call missing(uid);
end;
if customers.find()
then do;
  if devices.find()
  then do;
    counter + 1;
    uid = 'UID' !! put(counter,1.);
    rc = devices.add();
    rc = customers.add();
  end;
  else rc = customers.add();
end;
else do;
  if devices.find() then rc = devices.add();
end;
drop rc counter;
run;

proc sort data=want;
by uid cust;
run;

Re: SAS: unique identifier for multiple columns with same values

novinosrin — Mon, 10 Dec 2018 20:20:14 GMT


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
    ;
run;

data _null_;
if _n_=1 then do;
  dcl hash H (multidata:'yes') ;
   h.definekey  ("dev") ;
   h.definedata ("cust","dev","uid") ;
   h.definedone () ;
end;
do until(last.cust);
set have end=l;
by cust;
if _n_=1 then  do;
c=_n_;
uid =cats( 'UID', put(c,8. -l));
rc=h.add();
end;
else if not f and h.find()=0 then f=1;
end;
do until(last.cust);
set have ;
by cust;                             
if _n_>1 then do;
if f then rc=h.add();
else do;
if first.cust then do;c+1; uid =cats( 'UID', put(c,8. -l));end;
rc=h.add();
end;
end;
end;
if l then h.output(dataset:'want');
run;


proc sort data=want;
by uid cust dev;
run;

Re: SAS: unique identifier for multiple columns with same values

kilo_foxtrot — Tue, 11 Dec 2018 14:42:46 GMT

This is close but I'm seeing errors if I input additional rows to test. Take the following example where I've added Customer X Device 5:

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
    ;
run;

Your code provides the following results:

d	4	UID2
x	4	UID2
e	5	UID3
x	5	UID3

However, these should all have the same value of UID2 because Customer X is linked to Devices 4 and 5 which should also link Customer E and D.

Re: SAS: unique identifier for multiple columns with same values

kilo_foxtrot — Tue, 11 Dec 2018 14:44:26 GMT

Your answer is also very close but provides inaccuracies in the last four rows in the new test data in my reply above.

Re: SAS: unique identifier for multiple columns with same values

novinosrin — Tue, 11 Dec 2018 15:23:41 GMT

I realize my mistake, a bit busy now but will try later

Re: SAS: unique identifier for multiple columns with same values

kilo_foxtrot — Wed, 12 Dec 2018 16:39:37 GMT

Thank you -- I appreciate your efforts! I apologize I do not have an in-depth knowledge of hashes in SAS so I am unable to troubleshoot your first answer.

Re: SAS: unique identifier for multiple columns with same values

novinosrin — Wed, 12 Dec 2018 17:03:17 GMT

@kilo_foxtrot Don't worry about technology. I am working on your thread by seeking help from my college mate(who is super smart and solved the same in python) in handling networking problems. Once he gives me the understanding i need, I will try to code in SAS. No need to apologize. I love learning.

Re: SAS: unique identifier for multiple columns with same values

novinosrin — Thu, 13 Dec 2018 16:56:13 GMT

Hi @kilo_foxtrot First off, Sorry for the delay as I was consumed with a lot of my own stuff to do. Please see if the following works and let me know. I can further simplify and eliminate some redundancies. Right now, it is 10:10PM at Chicago and I am leaning on my pillows laying with my laptop in my lap. Feeling so tired, I haven't made it a complete neat one. Yet, I wanted to keep up my word and here you go-

/*Your new updated dataset HAVE*/
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
    ;
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;
	if not f1 then 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;
if not f1 then 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);
	end;
end;
if l then h.output(dataset:'w');
run;

/*Final Want	*/
proc sort data=w out=want nodupkey;
by uid __cust __dev;
run;

Please do let me know. And I will try to see to make some improvements when I feel fresh hopefully after a good sleep. Good night!

Re: SAS: unique identifier for multiple columns with same values

kilo_foxtrot — Thu, 13 Dec 2018 17:59:51 GMT

No need at all to apologize -- this is on your time! If I add Customer A Device 5, it should link every single record to be UID1. Your latest code doesn't quite get this right and it also adds two addition rows to the data:

/*Your new updated dataset HAVE -- new cust x dev 5*/
data have;
length cust dev $5;
input cust $ dev $;
datalines;
a 1
a 2
a 5
a 7
b 2
b 3
c 3
d 4
e 5
h 2
x 4
x 5
;
run;

__cust	__dev	uid
a	1	UID1
a	2	UID1
a	5	UID1
a	7	UID1
b	2	UID1
b	3	UID1
c	3	UID1
e	5	UID1
h	2	UID1
x	4	UID1
x	5	UID1
d	4	UID2
x	4	UID2
x	5	UID2

I can't thank you enough for your continued efforts on this -- it is getting very complex!

Re: SAS: unique identifier for multiple columns with same values

novinosrin — Thu, 13 Dec 2018 18:35:15 GMT

Hi @kilo_foxtrot Thank you. I request your patience(which you are, just saying again ) while we do this interactively and at the sametime I work on my stuff too. Trust me, yours is more interesting than mine and how i wish I devoted more time to test.

Nevetheless, I did a minor change and will wait for your feedback for further changes yet again

It's clear you are awesome in testing and that helps.

/*Your new updated dataset HAVE -- new cust x dev 5*/
data have;
length cust dev $5;
input cust $ dev $;
datalines;
a 1
a 2
a 5
a 7
b 2
b 3
c 3
d 4
e 5
h 2
x 4
x 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;
			else 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);
	end;
end;
if l then h.output(dataset:'w');
run;

/*Final Want	*/
proc sort data=w out=want nodupkey;
by uid __cust __dev;
run;

Re: SAS: unique identifier for multiple columns with same values

kilo_foxtrot — Thu, 13 Dec 2018 20:01:22 GMT

I appreciate your devotion to solving this problem @novinosrin. In my testing I try to "break" your code to help debug by adding/removing all kinds of customers and devices while knowing what to expect ahead of time. Your latest answer works for the test data, but it does not work for the previous example (removing Customer A Device 5).

I went back to the previous example (removing Customer A Device 5) that resulted in UID1 and UID2. I then added a new customer (Y) that is linked to UID1 via Device 7 and also linked to UID2 via Device 5. This should create a link among all records (all UID1) but again it creates extra records and assigns multiple UIDs. N=13 records in ‘have’, N=18 records in ‘want’.

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;

Again, your help is much appreciated! I'm sure it is tough to make robust since the test data provided probably does not offer enough data to thoroughly validate.

Re: SAS: unique identifier for multiple columns with same values

novinosrin — Thu, 13 Dec 2018 22:13:03 GMT

Hmm, i understand the bigger complexity now after staring at the example. Let me deal with simple piecemeal approach and respond.

Re: SAS: unique identifier for multiple columns with same values

Kurt_Bremser — Thu, 13 Dec 2018 22:15:43 GMT

I think this needs a kind of recursive approach, for which you will need to have the whole dataset in the hash.

Re: SAS: unique identifier for multiple columns with same values

novinosrin — Thu, 13 Dec 2018 22:36:37 GMT

Absolutely right sir @Kurt_Bremser . The idea is in my mind, just the references/syntax causes my eyes more strain as the evening unfolds. Excellent observation!

Re: SAS: unique identifier for multiple columns with same values

novinosrin — Fri, 14 Dec 2018 05:31:39 GMT

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

Re: SAS: unique identifier for multiple columns with same values

novinosrin — Fri, 14 Dec 2018 14:50:07 GMT

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

Re: SAS: unique identifier for multiple columns with same values

learsaas — Fri, 14 Dec 2018 08:19:34 GMT

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

Re: SAS: unique identifier for multiple columns with same values

novinosrin — Mon, 17 Dec 2018 22:19:51 GMT

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;