topic List NonDuplicated Name in the dataset within Comma seperation in SAS Programming

List NonDuplicated Name in the dataset within Comma seperation

ybz12003 — Thu, 27 May 2021 15:42:48 GMT

Good day Experts,

I created a sample dataset "Have." All the name are seperated with comma in the 'Name' column, however the names in the rows are varous. I would like to list all the nonDuplicated Name shown the 'Want' result. Please let me know how to procede. Thank you.

data Have;  
	length Name $100 ; 
	infile datalines delimiter='/'; 
	input Name;  
	datalines;                     
	John,Marry,Jacob, ,/
	,Ana,Marry,/
	Jacob,John,/
	Will,Tom,Micheal/
;   

data Want;  
	length NameNoDup $100 ; 
	infile datalines delimiter='/'; 
	input NameNoDup;  
	datalines;                     
	John/
	Marry/
	Jacob/
	Ana/
	Will/
	Tom/
	Micheal
;

Re: List NonDuplicated Name in the dataset within Comma seperation

PaigeMiller — Thu, 27 May 2021 15:52:00 GMT

data Have;  
	length Name $100 ; 
	infile datalines delimiter='/'; 
	input Name;  
	datalines;                     
	John,Marry,Jacob, ,/
	,Ana,Marry,/
	Jacob,John,/
	Will,Tom,Micheal/
;   

data want;
    set have;
    do i=1 to countw(name,',');
        newname=scan(name,i,',');
        output;
    end;
run;
proc sql;
    create table want2 as select distinct newname
    from want where not missing(newname);
run;

This raises the question, why are you creating data sets in such an unusual format? You would make your life much easier by creating a data set with one name per line, rather than many names per line.

Re: List NonDuplicated Name in the dataset within Comma seperation

ballardw — Thu, 27 May 2021 15:52:51 GMT

@ybz12003 wrote:

Good day Experts,

A

I created a sample dataset "Have." All the name are seperated with comma in the 'Name' column, however the names in the rows are varous. I would like to list all the nonDuplicated Name shown the 'Want' result. Please let me know how to procede. Thank you.
data Have;  
	length Name $100 ; 
	infile datalines delimiter='/'; 
	input Name;  
	datalines;                     
	John,Marry,Jacob, ,/
	,Ana,Marry,/
	Jacob,John,/
	Will,Tom,Micheal/
;   

data Want;  
	length NameNoDup $100 ; 
	infile datalines delimiter='/'; 
	input NameNoDup;  
	datalines;                     
	John/
	Marry/
	Jacob/
	Ana/
	Will/
	Tom/
	Micheal
;   

If the order isn't particularly critical

data need;
   set have;
   do i=1 to countw(name);
      onename = scan(name,i);
      if not missing(name) then output;
   end;
   drop i name;
run;

proc sql;
   create table want as
   select distinct onename as name
   from need
   ;
quit;

Default output is likely to be alphabetical by name.

Re: List NonDuplicated Name in the dataset within Comma seperation

Reeza — Thu, 27 May 2021 15:57:27 GMT

data Have;  
	length Name $100 ; 
	infile datalines delimiter=',/' ; 
	input Name @@;  
	datalines;                     
	John,Marry,Jacob, ,/
	,Ana,Marry,/
	Jacob,John,/
	Will,Tom,Micheal/
;  

proc sort data=have nodupkey;
where not missing(compress(trim(name), , 's'));
by name;
run;

proc print data=have;
run;

Read them directly into individual values?

Re: List NonDuplicated Name in the dataset within Comma seperation

novinosrin — Thu, 27 May 2021 16:03:45 GMT

data Have;  
	length Name $100 ; 
	infile datalines delimiter='/'; 
	input Name;  
	datalines;                     
	John,Marry,Jacob, ,/
	,Ana,Marry,/
	Jacob,John,/
	Will,Tom,Micheal/
;   

data _null_;
 if _n_=1 then do;
  dcl hash h();
  h.definekey('newname');
  h.definedone();
 end;
 set have end=z;
 do _n_=1 to countw(name,',');
   newname=scan(name,_n_,',');
   if newname>' ' then rc=h.add();
 end;
 if z;
 h.output(dataset:'want');
run;

proc print noobs;run;

Re: List NonDuplicated Name in the dataset within Comma seperation

ybz12003 — Thu, 27 May 2021 18:37:22 GMT

Hi all, I tried all your coding with my actual dataset. For some reason, PaigeMiller's code works better. Thank you so much for all of your time and effort.