BookmarkSubscribeRSS Feed
Alkibiades
Obsidian | Level 7

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?

3 REPLIES 3
LinusH
Tourmaline | Level 20

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
Alkibiades
Obsidian | Level 7
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.
LinusH
Tourmaline | Level 20

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 3 replies
  • 3822 views
  • 0 likes
  • 2 in conversation