Date:         Tue, 30 Mar 2010 09:28:42 -0700
Reply-To:     MichelleZ <michelle_zunnurain@HOTMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         MichelleZ <michelle_zunnurain@HOTMAIL.COM>
Organization: http://groups.google.com
Subject:      merge/sql/hash
Comments: To: sas-l@uga.edu
Content-Type: text/plain; charset=ISO-8859-1

Hello,

I'm trying to learn some hash object....The original was written in a
merge, I tried proc sql and it is acceptable using left join, just
wondering how to get the left join using hash object..


data address;
addr_key=10000001;
address='test address1';
output;
addr_key=10000002;
address='test address2';
output;
addr_key=10000003;
address='test address3';
output;
run;

proc print u;
title 'address';
run;


data provzip;
addr_key=10000001;
postl_cd='87048';
output;
addr_key=10000002;
postl_cd='39735';
output;
run;

proc print u;
title 'provzip';
run;


data hash1 (drop=rc);
length postl_cd $5;
declare AssociativeArray hh () ;
rc = hh.DefineKey ( 'addr_key' ) ;
rc = hh.DefineData ( 'addr_key', 'address' ) ;
rc = hh.DefineDone () ;
call missing(postl_cd);
do until ( eof1 ) ;
set address end = eof1 ;
rc = hh.add () ;
end ;
do until ( eof2 ) ;
set provzip end = eof2 ;
rc = hh.find () ;
if rc ^=0 then postl_cd=' ';
if rc =0 then output;
end ;
run ;

proc print data=hash1;
title 'hash1';
run;

data hash2;
if _n_ = 1 then do;
if 0 then set address;
declare hash lookup(dataset:'address');
lookup.defineKey('addr_key');
lookup.definedata('address');
lookup.defineDone();
end;

set provzip;
if lookup.find(key:addr_key) ne 0 then postl_cd=' ';
run;


PROC PRINT;
title 'hash2';
RUN;

proc sql;
create table sql1 as
select *
from address a
left join provzip b
on a.addr_key=b.addr_key
;
quit;
run;

proc print u;
title 'sql';
run;