Help using Base SAS procedures

DS2 hash and hash iterator packages

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 7
Accepted Solution

DS2 hash and hash iterator packages

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;


Accepted Solutions
Solution
‎04-22-2015 10:09 PM
Trusted Advisor
Posts: 1,300

Re: DS2 hash and hash iterator packages

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']
);

View solution in original post


All Replies
Trusted Advisor
Posts: 1,300

Re: DS2 hash and hash iterator packages

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;

Solution
‎04-22-2015 10:09 PM
Trusted Advisor
Posts: 1,300

Re: DS2 hash and hash iterator packages

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']
);

Occasional Contributor
Posts: 7

Re: DS2 hash and hash iterator packages

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...


Trusted Advisor
Posts: 1,300

Re: DS2 hash and hash iterator packages

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.

Occasional Contributor
Posts: 7

Re: DS2 hash and hash iterator packages

Thanks.

Super Contributor
Posts: 376

Re: DS2 hash and hash iterator packages

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?

Super Contributor
Posts: 376

Re: DS2 hash and hash iterator packages

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!

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 1071 views
  • 0 likes
  • 3 in conversation