topic Re: DI Studio Lookup Transformation - Not in Lookup Table in SAS Data Management

DI Studio Lookup Transformation - Not in Lookup Table

Alkibiades — Tue, 20 Sep 2016 09:11:27 GMT

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?

Re: DI Studio Lookup Transformation - Not in Lookup Table

LinusH — Tue, 20 Sep 2016 09:25:59 GMT

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.

Re: DI Studio Lookup Transformation - Not in Lookup Table

Alkibiades — Tue, 20 Sep 2016 09:56:37 GMT

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.

Re: DI Studio Lookup Transformation - Not in Lookup Table

LinusH — Tue, 20 Sep 2016 11:29:01 GMT

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?