SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

DI Studio Lookup Transformation - Not in Lookup Table

Reply
Occasional Contributor
Posts: 8

DI Studio Lookup Transformation - Not in Lookup Table

The lookup transformation works lovely if you want to check if values are present in the Lookup table. But what if I want to check if a key is not present in the lookup table and keep these rows?

 

In my example I have two lookup tables. I want my records of the source table to be present in one lookup table but absent absent in the other lookup table. 

 

The  code to my transformation looks like this:

   set isdata.ISDV_AKT end = eof;
   
   /* Is the current key value stored in hash h0?  */ 
   rc0 = h0.check(key: bnr, 
   key: COMP);
   /* Is the current key value stored in hash h1?  */ 
   rc1 = h1.check(key: bnr, 
   key: COMP);
   /* Examine success of lookups  */ 
   if ( rc0=0 and rc1=0 ) then 
   do; 
      /* Write row to target  */ 
      output work.festgeworden; 
   end; 

But I want the "Examine success of lookups" line to look like this:

  if ( rc0 = 0 and rc1 ^= 0 ) then 

Is there an option to keep the rows if the check is negative (rc1 ^= 0) and skip those who were checked successfully?

 

Or do I have to use user written code in this case?

Super User
Posts: 5,424

Re: DI Studio Lookup Transformation - Not in Lookup Table

Posted in reply to Alkibiades

I don't have DI Studio at my fingertips, but from the top of my head I don't think that Lookup can give you this.

Perhaps you could use the Data Validation transform, or use Join with a NOT EXISTS/NOT IN() construct.

 

What is your goal to sort keep those records? Perhaps there's another way, depending on your objectives.

Data never sleeps
Occasional Contributor
Posts: 8

Re: DI Studio Lookup Transformation - Not in Lookup Table

I am used to juggle with hashes in Enterprise Guide. We just recently started to develop with the DI Studio so I am still struggling a bit with the boundaries of the Lookup transformation. I often work with exclusions via hash checks and tried to emulate the same functionaliyty).
The issue here is, we work with pairs of keys, but subquery (which would be my second choice in a proc sql) and the like only seem to work with one single key. So I tried to resort to Lookup transformations to achieve a lookup with multiple keys.
Super User
Posts: 5,424

Re: DI Studio Lookup Transformation - Not in Lookup Table

Posted in reply to Alkibiades

An idea, do the Lookup, and use Extract in the next step to filter out missing keys.

Again, how will you use entries without keys? 

Data never sleeps
Ask a Question
Discussion stats
  • 3 replies
  • 781 views
  • 0 likes
  • 2 in conversation