topic Re: Duduplication accross rows in SAS Programming

Duduplication accross rows

frupaul — Mon, 20 Aug 2018 16:00:46 GMT

Hi everyone,

A bit of an interesting issue. I have the following data set:

Use the below code to generate table above:

  data Hierarchy;
 INFILE DATALINES DLM='' missover;
 length CustID $10 L1 $20 L2 $20 L3 $20 L4 $20 L5 $20 L6 $20 L7 $20 L8 $20;
 input CustID  $ L1 $ L2 $ L3 $ L4 $ L5 $ L6 $  L7 $ L8 $;
 cards;
 1 James James Luke Tom Anthony Matthew Jake Brudhy
 2 Peter Peter Raymond Raymond Peter Luke
 ;

Objective:

My objective is to deduplicate across rows. Across each row, if the values for any successive levels (L1,L2...) are the same, I would like to delete the higher level i.e. if L1 and L2 contain same values, then delete L2. However, since blanks can only be present after the last value in each row and not within the row, where duplicates are found and deleted, I would like to shift the values on the right of the empty cell to the left to fill up the deleted cell (arrow in above diagram shows this).

After doing that, final table should look like this.

All empty cells are to the left end of each row.

Does anyone know how to accomplish this?

Thanks,

Re: Duduplication accross rows

mkeintz — Mon, 20 Aug 2018 17:21:04 GMT

Let's go into pedagogical mode:

For each row (each "observation" in data step lingo) you want to iteratively do the same process to every successive pairs of neighboring values, namely

if "neighboring" values match, then shift to the left all subsequent (further right) neighbors, making sure to replace the right-most value with a blank.
either recheck the current pair of neighbors or (if you know you never have triplets) immediately go to the next pair. By "next pair" I mean step one name to the right - i.e. right-side of current pair becomes left-side next pair.

To implement iterative processes, examine the do loop. For 8 variables, you will want to loop through this process up to 7 times (L1 vs L2, then L2 vs L3, .... L7 vs L8).

And to facilitate support of the do loop for your task, learn about declaring the list of variables L1, L2, ... L8 as an array, via the ARRAY statement. Once associated with an array, the variables L1-L8 can not only be accessed by their own names, but also as element 1 of the array, element 2 of the array, etc. I.e. you can dismiss using specific variables names, and instead use a loop where you compare element i vs element i+1, and then just increment i in the loop.

Re: Duduplication accross rows

novinosrin — Mon, 20 Aug 2018 17:24:39 GMT

data Hierarchy;
 INFILE DATALINES DLM='' missover;
 length CustID $10 L1 $20 L2 $20 L3 $20 L4 $20 L5 $20 L6 $20 L7 $20 L8 $20;
 input CustID  $ L1 $ L2 $ L3 $ L4 $ L5 $ L6 $  L7 $ L8 $;
 cards;
 1 James James Luke Tom Anthony Matthew Jake Brudhy
 2 Peter Peter Raymond Raymond Peter Luke
 ;


proc transpose data=Hierarchy out=_Hierarchy;
by custid;
var l:;
run;
data want;
set _Hierarchy;
by custid col1 notsorted;
array L(8) $ ;
retain l;
if first.custid then do; n=0;call missing(of l(*));end;
if first.col1 then n+1; L(n)=col1;
if last.custid;
drop n col1 _name_ ;
run;

Re: Duduplication accross rows

frupaul — Mon, 20 Aug 2018 18:33:49 GMT

Hi Novinosrin,

Thanks for the solution. I am dealing with 100000 records. Will this proposed approach work on such high volume data?

Re: Duduplication accross rows

frupaul — Mon, 20 Aug 2018 18:34:59 GMT

HI Novinosrin

Thanks for the solution. I am dealing with 100,000 records. Will this proposed approach work on such high volume data?

Re: Duduplication accross rows

novinosrin — Mon, 20 Aug 2018 18:35:51 GMT

Please test and let the community know. If it's not robust, I am sure you will have get more efficient solutions, however until then play with it

Re: Duduplication accross rows

novinosrin — Mon, 20 Aug 2018 20:11:08 GMT

Some fun

data want;
if _n_=1 then do;
length k $8;
  dcl hash H (multidata:'y') ;
   h.definekey  ("custid") ;
   h.definedata ("custid","k") ;
   h.definedone () ;
   call missing(k);
end;
set Hierarchy ;
array t(*) l:;
do i=1 to dim(t);
if not missing(t(i)) then 
if  i=1 or  t(i) ne t(i-1) then h.add(key:custid,data:custid,data:t(i));
end;
do i=1 by 1 while(h.do_over() eq 0);
t(i)=k;
end;
do i=i to dim(t);
 call missing(t(i));
end;
keep custid l:;
run;

Re: Duduplication accross rows

novinosrin — Tue, 21 Aug 2018 00:19:25 GMT

Cleaned further and edited some redundant step:

data want;
if _n_=1 then do;
length k $8;
  dcl hash H (multidata:'y') ;
   h.definekey  ("custid") ;
   h.definedata ("k") ;
   h.definedone () ;
   call missing(k);
end;
set Hierarchy ;
array t(*) l:;
do i=1 to dim(t);
if not missing(t(i)) then 
if  i=1 or  t(i) ne t(i-1) then h.add(key:custid,data:t(i));
end;
call missing(of t(*));
do i=1 by 1 while(h.do_over() eq 0);
t(i)=k;
end;
keep custid l:;
run;

Re: Duduplication accross rows

FreelanceReinh — Tue, 21 Aug 2018 09:16:47 GMT

@novinosrin: Well done. The revised version is more efficient, but interestingly it seems to be still a bit slower than your two-step solution using PROC TRANSPOSE (at least on my computer; probably also depending on input data): With an input dataset containing 10 million observations the latter took 24.0 s vs. 26.4 s for the hash solution, which in turn was faster than the below solution using the (slow) PRXCHANGE function (27.3 s) -- all run times averaged over three runs.

data want(drop=c i);
length c $176;
set Hierarchy;
array l[8];
c=prxchange('s/(~[^~]+~)(\1)+/\1/', -1, '~'||catx('~~',of l:)||'~');
do i=1 to dim(l);
  l[i]=scan(c,i,'~');
end;
run;

(To avoid truncation, I increased the length specifications for variable k in your hash solution and array L in your two-step solution to $20.)

Re: Duduplication accross rows

frupaul — Tue, 21 Aug 2018 09:27:18 GMT

Respect mate. This will have to send me back to school. Short and sleek. Although I have no idea how it works, it works.

Thanks

Re: Duduplication accross rows

FreelanceReinh — Tue, 21 Aug 2018 10:19:59 GMT

Thanks, @frupaul.

I don't use the PRX functions frequently, so it took me some time to (hopefully) get the regex right.

The third argument of PRXCHANGE encloses the values of L1, ..., L8 in '~' characters (which must not occur in any of the values) and concatenates the results. The resulting string (with a maximum length of (20+2)*8=176) is then searched for a string of the form "~more characters~" (where "more characters" may contain anything but the '~': "[^~]+" means "one or more non-tilde characters") which is followed by one or more occurrences of the same string (this is the "(\1)+" part). If such a duplicate (or triplicate, ...) string is found, it is replaced by only one copy (the final "\1" in the regex). The search/replace operation is continued until the end of the input string is reached (second argument -1).

The enclosing characters (I chose '~' because it is not one of the PRX metacharacters) were necessary to avoid partial matches such as "James" in "James Miller".

(I was tempted to use CALL POKELONG to write the final result to L1-L8 -- which could have shortened the code further [no array, no DO loop] -- but since CATX does not provide an option to preserve trailing blanks, that was not successful.)

Re: Duduplication accross rows

frupaul — Tue, 21 Aug 2018 10:30:22 GMT

Hi FreelanceReinhard,

Thanks for the explanation, it makes sense but will become much clearer once I have read the PRX functions.

I will be putting up another question in 5 minutes, along the same line of reasoning as this. But that one will be really challenging. Please share your thoughts when it comes.

Thanks

Re: Duduplication accross rows

novinosrin — Tue, 21 Aug 2018 12:02:24 GMT

@FreelanceReinh Thank you Sir, as always for the details. Your diligence teaches me a lot. And I can't thank you enough for your time in a lot of thread to help me learn a lot. Just waking up in Chicago. Good morning to you. I hope you have a nice day.

Re: Duduplication accross rows

Ksharp — Tue, 21 Aug 2018 13:12:13 GMT

data Hierarchy;
 INFILE DATALINES DLM='' missover;
 length CustID $10 L1 $20 L2 $20 L3 $20 L4 $20 L5 $20 L6 $20 L7 $20 L8 $20;
 input CustID  $ L1 $ L2 $ L3 $ L4 $ L5 $ L6 $  L7 $ L8 $;
 cards;
 1 James James Luke Tom Anthony Matthew Jake Brudhy
 2 Peter Peter Raymond Raymond Peter Luke
 ;
data want;
 set Hierarchy;
 array x{*} $ L:;
 array y{9999} $ 100 _temporary_;
 call missing(of y{*});
 j=0;
 do i=1 to dim(x);
   if x{i} not in y then do;j+1;y{j}=x{i};end;
 end;
 do i=1 to dim(x);
   x{i}=y{i};
 end;
 drop i j;
run;

Re: Duduplication accross rows

FreelanceReinh — Tue, 21 Aug 2018 13:17:42 GMT

You're welcome, @novinosrin. It's a mutual learning experience. So, thank you, too.

Re: Duduplication accross rows

frupaul — Wed, 22 Aug 2018 06:10:45 GMT

Hi Novinosrin,

I will be posting an extension to this problem. Just realised the initial post was an over simplification of the issue. Please do chip in.

The post will have a similar subject to this one.

Thanks

Re: Duduplication accross rows

novinosrin — Wed, 22 Aug 2018 11:34:10 GMT

Good morning, Sure

Re: Duduplication accross rows

frupaul — Wed, 22 Aug 2018 15:04:28 GMT

HI Novinosrin,

Please can you recommend a book/journal/publication on Hash Object that can take me from beginner to pro?

Thanks,

Paul

Re: Duduplication accross rows

novinosrin — Wed, 22 Aug 2018 15:24:33 GMT

Good morning @frupaul Here it is

https://communities.sas.com/t5/Community-Matters/Book-Data-Management-Solutions-Using-SAS-Hash-Table-Operations-A/m-p/433642#M3007

Nothing /Nobody beats that book or Paul Dorfman. Do read that, you are the next Paul as your name already being paul with prefix fru will bear fruit.

By the way, I have just made it to my college lab as the morning trains were running late. I saw your other post and I noticed few responses. Since we both like hash, I will have a fun response with hash too in a bit

Re: Duduplication accross rows

FreelanceReinh — Wed, 22 Aug 2018 16:11:51 GMT

Hi @frupaul,

The authors of the book @novinosrin has recommended are of course world-class SAS programmers and you can learn many SAS tricks from their long professional experience.

To get a taste of their writing style (and, above all, of the hash object) you could read their 2017 paper Beyond Table Lookup: The Versatile SAS® Hash Object, which is closely related to their book.

When I started learning hash object programming there was only Michele M. Burlew's book "SAS® Hash Object Programming Made Easy". In October 2016 I wrote a customer review (titled "Valuable resource for intermediate and advanced SAS programmers") about it on a very well known online bookseller's website.

As the hash object has become one of my must-have tools, I've recently purchased the PDF version of the Dorfman/Henderson book (as a sort of "next-level sequel").