topic Re: Using SAS query in Impala in SAS Programming

Using SAS query in Impala

Tomcaty — Wed, 21 Mar 2018 03:28:18 GMT

Hello all,

I am trying to replicate the attached SAS query in Impala. Basically, I want to pull all the observations from a dataset having a key variable "ID". Is there a way to replicate the same query using proc sql or may be in Impala.

data want;
  if _n_=1 then do;
    declare hash m(dataset:"keyvariable");
    m.definekey('ID');
    m.definedone();
  end;
  set have; 
  if m.find()=0;
run;

Thanks for your help,

Tom

Re: Using SAS query in Impala

ChrisNZ — Wed, 21 Mar 2018 04:24:31 GMT

How many values in the table keyvariable?

Re: Using SAS query in Impala

LinusH — Wed, 21 Mar 2018 11:01:33 GMT

Your code is written as you are working with data in SASWORK, but perhaps that's a simplification...?
Also, forgive me my ignorance in hash table syntax, but are you keeping records that doesn't have match?
If so, and 'have is already in Impala: make sure that your lookup table is there as well. Then use the SQL syntax WHERE id NOT IN (select id from keyvariable)