DS2 Hash Package Output Overwrite

[slacey]

In the output method for the hash package, how do I specify overwrite? (is it even possible?). For example, the following code runs fine the first time:

proc ds2;
	data _null_;
		dcl char(20) USUBJID;
		dcl char(8) VSTESTCD;
		dcl char(20) VISIT;
		dcl char(30) VSTPT;
		dcl double rc;

		dcl package hash hw();

		method init();
			hw.ordered('a');
			hw.keys([USUBJID VSTESTCD VISIT VSTPT]);
			hw.dataset(%tslit({select * 
					   from sdtm.vs 
					   where VSTESTCD in ('HEIGHT','WEIGHT')}));
			hw.definedone();
		end;

		method term();
			rc = hw.output('testtest5');
		end;
	enddata;
	run;
quit;

However if I run again the dataset that hw.output tries to use produces an error since it already exists.

Thanks

[ChrisNZ · Accepted solution]

I don't think such an option exists but might be wrong.

You can work around that by calling function dosubl() to run proc delete before calling the output method.

[slacey]

Looks like dosubl isn't recognized in proc ds2 (or it's very possible i was using it wrong). However I was able to just pass it through a sysfunc macro call like so:

		%if %sysfunc(exist(testtest1)) %then %do;
			%sysfunc(dosubl('proc delete lib=work data=testtest1; run;'));
		%end;

Thank you for the suggestion!

[Tom]

---

@slacey wrote:

Looks like dosubl isn't recognized in proc ds2 (or it's very possible i was using it wrong). However I was able to just pass it through a sysfunc macro call like so:

		%if %sysfunc(exist(testtest1)) %then %do;
			%sysfunc(dosubl('proc delete lib=work data=testtest1; run;'));
		%end;

Thank you for the suggestion!

---

Why would you need DOSUBL in that case?  How is running that macro code different than running this macro code?

%if %sysfunc(exist(testtest1)) %then %do;
  proc delete lib=work data=testtest1; run;
%end;

Or this SAS code (note that PROC DELETE does not have a NOWARN option, but PROC DATASETS does).

proc datasets nowarn nolist lib=work;
  delete testtest1;
quit;

[slacey]

You're 100% correct. I wrote the reply in the middle of working on it and came across that exact realization afterwards. In the end I just wrapped the proc delete in the macro exist call and put it all before proc ds2.

[AhmedAl_Attar]

@slacey 

You probably can add the following before your Proc DS2 statement

%if (%sysfunc(exist(testtest5))) %then
%do;
   PROC DELETE Data=testtest5; run;
%end;

[yabwon]

Use the Force 😉

When a Jedi Knight uses PROC FEDSQL, the proc just deletes data set regardless the data set exists or not 🙂

data class; 
  set sashelp.class;
run;


PROC FEDSQL;
   DROP TABLE TESTTEST5 FORCE; /* no need for conditional execution */
QUIT;

proc ds2;
	data _null_ / OVERWRITE=YES;
		dcl char(20) name;
		dcl char(8) sex;
		dcl double AGE;
		dcl double rc;

		dcl package hash hw();

		method init();
			hw.ordered('a');
			hw.keys([name age]);
			hw.dataset(%tslit({select * 
					   from work.class 
					   where sex in ('F','M')}));
			hw.definedone();
		end;

		method term();
			rc = hw.output('testtest5');
		end;
	enddata;
	run;
quit;

May the Force be with you!

Bart

[slacey]

I really like that. I've been using SAS for years now and it's enlightening to see how much I don't know.