DS2 hash and hash iterator packages

[gnanau]

I am looking for an example on how to use a hash and hash iterator to iterate through a data set inside a DS2 data program. As practical application I would like to build the Cartesian product of two tables.

Each of the tables is a list of ID and Value and I want in face to end up with a data set containing all possible pairs of these 2 sets of Value.

I tried something like below but is not working. It looks like rc=hi.first(); returns an error code rc=4 but I cannot find anywhere what that means.

proc ds2;
data one (overwrite=YES);
method run();
  set post.workPC (RENAME=(Value=Value1 TblID=TblID1));
end;
enddata;
run;

data two (overwrite=YES);
method run();
  set post.workPC (RENAME=(Value=Value2 TblID=TblID2));
  if TblID2 gt 1 then output;
end;
enddata;
run;

data prodcart (overwrite=Yes);
declare double TblID2 rc;
declare char(10) Value2;

declare package hash h ('two');
declare package hiter hi('h');

method init();

   rc = h.defineKey('TblID2');
    rc = h.defineData('Value2');
    rc = h.defineDone();

  end;

method run();
set one;
rc=hi.first();
put TblID2 Value2 TblID1 Value1;

end;

run;
quit;

[FriedEgg]

data class;

set sashelp.class;

run;

data altclass;

set sashelp.class;

run;

proc ds2;

data foo (overwrite=yes);

dcl nchar(32) altname;

dcl package hash h(8, '{SELECT name as altname FROM altclass}');

dcl package hiter hi('h');

method init();

dcl double rc;

rc = h.defineKey('altname');

rc = h.defineData('altname');

rc = h.defineDone();

end;

method run();

dcl double rc;

set class;

rc = hi.first();

do while(rc=0);

output;

rc = hi.next();

end;

end;

enddata;

run;

quit;

[FriedEgg · Accepted solution]

your specific issue is that you are not declaring the hash with a valid syntax (you are missing a definition for hashexp), apparently this does not cause any kind of error to be produced, you just get a empty hash.

SAS(R) 9.4 DS2 Language Reference, Fourth Edition

Form 1:

DECLARE PACKAGE HASH variable ( );

Form 2:

DECLARE PACKAGE HASH variable (hashexp, {'datasource' | '\{sql-text\}'},
'ordered', 'duplicate ', 'suminc', 'multidata');

Form 3:

DECLARE PACKAGE HASH variable ( \[keys\], \[data\], [hashexp,
{'datasource' | '\{sql-text\}'}, 'ordered', 'duplicate ', 'suminc', 'multidata']);

Form 4:

DECLARE PACKAGE HASH variable ( \[keys\], [hashexp, {'datasource' | '\{sql-text\}'},
'ordered', 'duplicate ', 'suminc', 'multidata']);

[gnanau]

Yes, you are right, FriedEgg !                                     

Thank you very much...

Do you know if I can find anywhere what different values for the return code mean? In my case I was getting rc=4 but I cannot seem to find anywhere what these individual codes are...

[FriedEgg]

The return codes are not documented, to my knowledge.  An appropriate error message should be displayed if you exclude the rc=, if it behaves like the hash object in non-ds2 data step.  For the `first` method basic meaning is: RC=0 = success, RC ≠ 0 = failure.

[gnanau]

Thanks.

[ScottBass]

I tried the tiniest of variations of this, as follows:

data class;
   set sashelp.class;
run;
data altclass;
   set sashelp.class;
run;
proc ds2;
   data foo (overwrite=yes);
      dcl char(32) altname;
      dcl package hash h(8, '{SELECT name as altname,age,sex FROM altclass}');
      dcl package hiter hi('h');
      method init();
         dcl double rc;
         rc = h.defineKey('altname');
         rc = h.defineData('altname');
         rc = h.defineDone();
      end;
      method run();
         dcl double rc;
         set class;
         rc = hi.first();
         do while(rc=0);
            output;
            rc = hi.next();
         end;
      end;
   enddata;
   run;
quit;
data &syslast;
   set &syslast;
run;
data foo;
   set foo;
run;

That works, although it's "irritating" that proc ds2 does not properly set &syslast.

data fact (keep=stock where=(stock='IBM'));
   set sashelp.stocks;
run;
data lookup;
   set sashelp.stocks;
run;
* check the sql - it works ;
proc sql;
   SELECT stock,open,close FROM lookup;
quit;
proc ds2;
   data foo (overwrite=yes);
      dcl char(9) stock;
      dcl double open close;
      dcl package hash h(8, '{SELECT stock,open,close FROM lookup}'); * so why does this fail??? ;
      dcl package hiter hi('h');
      method init();
         dcl double rc;
         rc = h.defineKey('stock');
         rc = h.defineData('open');
         rc = h.defineData('close');
         rc = h.defineDone();
      end;
      method run();
         dcl double rc;
         set fact;
         rc = hi.first();
         do while(rc=0);
            output;
            rc = hi.next();
         end;
      end;
   enddata;
   run;
quit;
data &syslast;
   set &syslast;
run;
data foo;
   set foo;
run;

But this fails:

ERROR: Syntax error at or near "open"
ERROR: Unable to prepare statement for hash data source {SELECT stock,open,close FROM lookup}.
ERROR: Error reported by DS2 package d2hash:
ERROR: Hash data source load failed.
ERROR: Fatal run-time error.
ERROR: General error

Can anyone translate this error message?

[ScottBass]

Ok, this works:

proc ds2;
   data foo (overwrite=yes);
      dcl char(9) stock;
      dcl double open close;
      dcl package hash h(8, '{SELECT stock,"open","close" FROM lookup}'); * so why does this fail??? ;
      dcl package hiter hi('h');
      method init();
         dcl double rc;
         rc = h.defineKey('stock');
         rc = h.defineData('open');
         rc = h.defineData('close');
         rc = h.defineDone();
      end;
      method run();
         dcl double rc;
         set fact;
         rc = hi.first();
         do while(rc=0);
            output;
            rc = hi.next();
         end;
      end;
   enddata;
   run;
quit;

"open" and "close" are not listed as reserved words in Ch 21 "DS2 Reserved Words" of the DS2 Language Reference.

However, they are listed as reserved words in the FedSQL documentation:  http://support.sas.com/documentation/cdl/en/fedsqlref/67364/HTML/default/viewer.htm#p0x0k1rlx0l7e4n1...

A link to that documentation from Ch 21 would be useful.

SAS R&D - would it be that hard to say something like "open is a reserved word" in the error message???  Clear and consise error messages make your customers' and Tech Support's life a lot easier!