topic Re: SAS Index Merge in SAS Programming

SAS Index Merge

sfffdg — Wed, 21 Apr 2021 06:07:59 GMT

Hi ,

The data set UPDATE_EMAIL_ltst has c_id, email_id variables

The data set dcclnt has c_id , email_id and p_c_id variables

c_id, email_id are the key variables

for the below query ,I am getting previous row data for p_c_id instead the current record data.

Can any one help with this.

rsubmit;
data Email ;
set UPDATE_EMAIL_ltst ;
set dcclnt key=Clt_Email /unique;
if _error_=0 then
do;
delete;
end;
else do;
_error_=0;
output ;
end;
run;
endrsubmit;

Re: SAS Index Merge

mkeintz — Wed, 21 Apr 2021 06:54:07 GMT

Sample data, in the form of working data steps, please. Help us help you.

Re: SAS Index Merge

mkeintz — Wed, 21 Apr 2021 07:09:27 GMT

OK, I figured this out without provision of sample data.

All variables retrieved via a SET statement are retained (i.e. held over to the next data step iteration (usually the next observaton).
But a successful SET overwrites the retained values from the prior obs with new values.
An unsuccessful SET does NOT overwrite the retained values.

So your unsuccessful SET /key= yields a non-zero _ERROR_ . In such cases, you have chosen to reset _ERROR_ to zero, but you also need to set the retained P_C_ID to missing.

Re: SAS Index Merge

ChrisNZ — Wed, 21 Apr 2021 07:34:10 GMT

Format your code, this is illegible. There's an icon for this to use when pasting.

rsubmit;                                           %* send to remote host;
  data EMAIL ;                                     %* create table EMAIL;
    set UPDATE_EMAIL_LIST ;                        %* read table UPDATE_EMAIL_LIST ;
    set DCCLNT key=CLTEMAIL /unique;               %* read table DCCLNT using key CLTEMAIL ;
    if _ERROR_=0 then do;                          %* if a record is found in DCCLNT ;
      delete;                                      %* delete (i.e. ignore) the record read in UPDATE_EMAIL_LIST ;
    end;
    else do;                                       %* else ;
      _ERROR_=0;                                   %* reset _ERROR_ flag ; 
      output ;                                     %* save the record read in UPDATE_EMAIL_LIST ;
    end;
  run;
endrsubmit;

So in effect: keep all records from UPDATE_EMAIL_LIST not present in DCCLNT

Re: SAS Index Merge

Kurt_Bremser — Wed, 21 Apr 2021 07:38:46 GMT

If I read that code correctly, you want to delete observations where the key is found in dcclnt?

In which case, a hash solves the issue nicely without needing to rely on the indexes:

data Email ;
set UPDATE_EMAIL_ltst;
if _n_ =1
then do;
  declare hash dcc (dataset:"dcclnt");
  dcc.definekey("Clt_Email");
  dcc.definedone();
end;
if dcc.find() ne 0;
run;

Re: SAS Index Merge

ChrisNZ — Wed, 21 Apr 2021 07:51:13 GMT

@Kurt_Bremser check() would be faster than find() here 🙂 .

Re: SAS Index Merge

sfffdg — Wed, 21 Apr 2021 09:20:45 GMT

Hi,

The data sample is below,the inputs and the output

In Email Out put data set P_Email_id should be ideally xyz1@gmail.com ,where as it is abc@gmail.com

Re: SAS Index Merge

sfffdg — Wed, 21 Apr 2021 09:53:17 GMT

The code is below

rsubmit;
data a;
input C_ID $ EMAIL_ID $20.;
cards;
C1 abc@gmail.com
C2 xyz@gmail.com

;
run;
endrsubmit;

rsubmit;
data b(index=(Clt_Email=(C_ID email_id)));
input C_ID $ EMAIL_ID $5-18 P_EMAIL_ID $20-33
;
cards;
C1 abc@gmail.com abc@gmail.com
C11 abc1@gmail.com abc1@gmail.com
C2 xyz1@gmail.com xyz1@gmail.com

;
run;
endrsubmit;

rsubmit;
data ab ;
set a ;
set b key=Clt_Email /unique;
if _error_=0 then
do;
delete;
end;
else do;
_error_=0;
output ;
end;
run;
endrsubmit;

Re: SAS Index Merge

Kurt_Bremser — Wed, 21 Apr 2021 10:15:05 GMT

This creates your expected dataset without indexes:

data a;
input C_ID $ EMAIL_ID :$20.;
cards;
C1 abc@gmail.com
C2 xyz@gmail.com
;

data b;
input C_ID $ EMAIL_ID :$20. P_EMAIL_ID :$20.;
cards;
C1 abc@gmail.com abc@gmail.com
C11 abc1@gmail.com abc1@gmail.com
C2 xyz1@gmail.com xyz1@gmail.com
;

data ab;
set a;
if _n_ = 1
then do;
  length _email_id P_EMAIL_ID $20;
  declare hash b (dataset:"b (rename=(email_id=_email_id))");
  b.definekey("c_id");
  b.definedata("_email_id","p_email_id");
  b.definedone();
  call missing(_email_id,P_EMAIL_ID);
end;
if b.find() = 0 and _email_id = email_id then delete;
drop _:;
run;

Re: SAS Index Merge

sfffdg — Wed, 21 Apr 2021 10:18:53 GMT

Thanks Kurt ,

But I get the below error.

ERROR: Undeclared key symbol Clt_Email for hash object at line 1019 column 3.
ERROR: DATA STEP Component Object failure. Aborted during the EXECUTION phase.

Re: SAS Index Merge

Kurt_Bremser — Wed, 21 Apr 2021 10:37:55 GMT

My code as posted works. Just copy/paste and run it.

Adapt it to your variables, or post example data with the real column names.

Note that the hash keys only on the id and uses the email in a separate comparison.

Re: SAS Index Merge

sfffdg — Wed, 21 Apr 2021 11:05:36 GMT

Thanks Kurt,

I just used the same code as below.

The whole code is as below

rsubmit;
data UPDATE_EMAIL_ltst(keep=client_id email_id );
set UPDATE_EMAIL;
email_id=upcase(event_value);
by client_id last_update_ts;
if last.client_id;
run;
endrsubmit;

rsubmit;
data dcclnt(keep=client_id polisy_email_id email_id index=(Clt_Email=(client_id email_id)));
set data.dcclnt(rename=(clt_key=client_id) where=(valid_flag='1') );
email_id=upcase(email_id);
polisy_email_id=email_id;
run;
endrsubmit;

rsubmit;
data Email_t;
set UPDATE_EMAIL_ltst;
if _n_ =1
then do;
declare hash dcc (dataset:"dcclnt");
dcc.definekey("client_id");
dcc.definedone();
end;
if dcc.find() ne 0;
run;
endrsubmit;

The error is below:

ERROR: Hash object added 32505840 items when memory failure occurred.
FATAL: Insufficient memory to execute DATA step program. Aborted during the EXECUTION phase.
ERROR: The SAS System stopped processing this step because of insufficient memory.

Re: SAS Index Merge

ChrisNZ — Wed, 21 Apr 2021 11:17:56 GMT

Try using something, like _N_, as hash data. Otherwise the key is used again as data and it's probably quite long.

Re: SAS Index Merge

Kurt_Bremser — Wed, 21 Apr 2021 11:18:52 GMT

How long is client_id defined? And what is your MEMSIZE option?

A quick calculation shows me that your ~33 million observations should fit in about 1.5 GB of RAM, unless the variable is longer than expected.

Re: SAS Index Merge

ChrisNZ — Wed, 21 Apr 2021 11:21:56 GMT

Kurt, hash tables have a significant per-record overhead. Look for macro hashsize or hash_size in these pages. Sorry can't look from my phone.

Re: SAS Index Merge

sfffdg — Wed, 21 Apr 2021 12:12:10 GMT

Hi Kurt,

The Id is 10chars

current MEMSIZE=2147483648

Re: SAS Index Merge

sfffdg — Wed, 21 Apr 2021 22:03:56 GMT

Hi Kurt ,

Thanks for the solution ,the problem with this approach is memory failure .

How can we change the below index merge query so that it will serve the purpose.

rsubmit;
data ab ;
set a ;
set b key=Clt_Email /unique;
if _error_=0 then
do;
delete;
end;
else do;
_error_=0;
output ;
end;
run;
endrsubmit;

Re: SAS Index Merge

ChrisNZ — Wed, 21 Apr 2021 22:08:31 GMT

Try adding line

dcc.definedata("_N_");

to reduce the memory footprint,

Re: SAS Index Merge

sfffdg — Wed, 21 Apr 2021 22:08:54 GMT

Hi Kurt,

Thanks for the solution given ,but when ran it gives the memory failure error.

How can we change the below index merge query so that it will serve the purpose,which is fast as well.

rsubmit;
data ab  ;
set a ;
set b key=Clt_Email /unique;
if _error_=0 then
	             do;
                    delete;
	             end;
            else do;
			        _error_=0; 
                    output ;
		         end; 
run;
endrsubmit;

Re: SAS Index Merge

ChrisNZ — Wed, 21 Apr 2021 22:10:49 GMT

> Can any one help with this.

You now know what the code does. Wasn't that your question?