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
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
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
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 :-?
Scott
I do not know why, since both are read only.
bruno
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 🙂
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
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: