hash join use external data as input

GeorgeSAS — Mon, 01 Aug 2016 20:56:26 GMT

Hello all;

There is a very large external flat file named external, I want to use it in hash join.how to use infile statement to do it?

Here is test code:

data external; /*I don't want to create this data set here,the data is too large,I want use infile in hash join step directly*/

infile cards;

input ptnum $ 1-3 @5 date date9. event $ 15-35;

format date date9.;

cards;

ABC 16NOV2009 Nausea

DEF 16NOV2009 Heartburn

DEF 16NOV2009 Acid Indigestion

DEF 18NOV2009 Nausea

GHI 17NOV2009 Fever

GHI 18NOV2009 Fever

MNO 17NOV2009 Fever

;

run;

data cm;

infile cards;

input ptnum $ 1-3 @5 date date9. medication $ 15-35;

format date date9.;

cards;

ABC 16NOV2009 Dopamine

DEF 16NOV2009 Antacid

DEF 16NOV2009 Sodium bicarbonate

aaa 18NOV2009 Dopamine

bbb 18NOV2009 Asprin

ccc 19NOV2009 Asprin

ddd 17NOV2009 Asprin

;

run;

Data ae_rspndt;

If _n_ = 1 then do;

if 0 then set cm ;

declare hash cm(dataset: "work.cm") ;

rc=cm.defineKey("ptnum", 'date');

rc=cm.defineData(ALL: 'YES');

rc=cm.defineDone() ;

end;

do until (eof);

set external end=eof;/*In this step, I want to change set external to infile statement*/

rc = cm.find();

if rc = 0 then do;

output ;

end;

stop;

Run;

I want change "set external" to directly use infile statement like:

Data ae_rspndt2;

If _n_ = 1 then do;

if 0 then set cm ;

*declare hash cm(dataset: "work.cm",hashexp:16,multidata:"Yes") ;

declare hash cm(dataset: "work.cm") ;

rc=cm.defineKey("ptnum", 'date');

rc=cm.defineData(ALL: 'YES');

rc=cm.defineDone() ;

end;

do until (eof);

infile "An--external--file" end=eof;

input ptnum $ 1-3 @5 date date9. event $ 15-35;

rc = cm.find();

if rc = 0 then do;

output ;

end;

stop;

Run;

But itnot works, please help.

Thanks!

Re: hash join use external data as input

KachiM — Mon, 01 Aug 2016 23:03:15 GMT

I have newly added an efficiency Note at the bottom of the post.

This may help you.

Data want;
   filename myext "C:\Users\K\ALLSAS\Prod\TEST.txt";
   If _n_ = 1 then do;
      if 0 then set cm ;
 
      declare hash cm(dataset: "work.cm") ;
      cm.defineKey("ptnum", 'date');
      cm.defineData(ALL: 'YES');
      cm.defineDone() ;
   end;
   do until(eof);

   infile myext end = eof ;
   input @1 ptnum $ 1-3 @5 date date9. @15 event &$ 15-35;
   format date date9.;
   if cm.find() = 0 then output;
   end;
   if eof then stop;
run;
proc print data = want;
run;

You place your text file in some external directory. The need of symbol & is a must.

I think the use of rc is not must in this application. The return code of find() can be checked without rc as done here. It is my style.

The Efficiency Note:

The variables that are KEY part of the hash table are common to both data set. The only variable that is needed to go the data part of the hash table is "Medication". Replacing

cm.defineData(ALL: 'YES');

cm.definedata('medication');

will save 16 bytes of memory for each item of hash table.

topic hash join use external data as input in SAS Programming

hash join use external data as input

Re: hash join use external data as input