Dynamic hash column definition using existing dataset in PROC DS2

[ScottBass]

Hi,

This works:

data class class_cols;
   set sashelp.class;
   output class;
run;

proc ds2;
   data test(overwrite=yes);
      dcl package hash h();
      dcl double num;
      method init();
         if 0 then set class_cols (locktable=share);
         h.definekey('name');
         h.definekey('sex');
         h.definedata('age');
         h.definedata('height');
         h.definedata('weight');
         h.dataset('{select * from class where sex=''F''}');
         h.definedone();
         num=h.num_items;
         put num=;
      end;
   enddata;
   run;
quit;
data test;set test;run;  * to get EG to display the results from DS2 ;

But this fails:

data class class_cols;
   set sashelp.class;
   output class;
run;

proc ds2;
   data test(overwrite=yes);
      dcl package hash h();
      dcl double num;
      method init();
         if 0 then set class (locktable=share);
         h.definekey('name');
         h.definekey('sex');
         h.definedata('age');
         h.definedata('height');
         h.definedata('weight');
         h.dataset('{select * from class where sex=''F''}');
         h.definedone();
         num=h.num_items;
         put num=;
      end;
   enddata;
   run;
quit;
data test;set test;run;

How do I get this 2nd example to work?  Is there a better way?  This approach is quite common in data step processing.

Thanks,

Scott

[BrunoMueller · Accepted solution]

Scott

I guess you need to set the LOCKTABLE= option on both statements. See also here FedSQL Statement Table Option Syntax

See an example below:

data class class_cols;
   set sashelp.class;
   output class;
run;

proc ds2;
   data test(overwrite=yes);
      dcl package hash h();
      dcl double num;
      method init();
         if 0 then set class (locktable=share);
         h.definekey('name');
         h.definekey('sex');
         h.definedata('age');
         h.definedata('height');
         h.definedata('weight');
         h.dataset('{select * from class {options LOCKTABLE= SHARE} where sex=''F''}');
         h.definedone();
         num=h.num_items;
         put num=;
      end;
   enddata;
   run;
quit;
data test;set test;run;

Bruno

[ScottBass]

Thanks that works, although I'm surprised that either the set statement or "select * from class" puts a lock on the table such that the other statement fais :-?

[BrunoMueller]

Scott

I do not know why, since both are read only.

bruno

[ScottBass]

Yep, my comment was more for any birdies that care to chime in.  Yes, since SET and SELECT are both readonly, and explicitly supplying LOCKTABLE=SHARE works, why couldn't the underlying code do that, i.e. make both opens of the table "shared"?

Rhetorical question, no need to reply, but if any birdies want to chime in...or change the code...feel free 🙂

[BrunoMueller]

Hi Scott

After reading the doc on the LOCKTABLE= table option comes the explantion

LOCKTABLE= Table Option

Details
You can lock tables only if you are the owner or have been granted the necessary privilege.
If you use PROC DS2, the default value for the LOCKTABLE option is EXCLUSIVE.

Bruno