topic Re: DS2 Hash Package Output Overwrite in SAS Programming

DS2 Hash Package Output Overwrite

slacey — Thu, 19 Sep 2024 18:49:46 GMT

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

Re: DS2 Hash Package Output Overwrite

ChrisNZ — Thu, 19 Sep 2024 20:00:13 GMT

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.

Re: DS2 Hash Package Output Overwrite

AhmedAl_Attar — Thu, 19 Sep 2024 20:14:30 GMT

@slacey

You probably can add the following before your Proc DS2 statement

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

Re: DS2 Hash Package Output Overwrite

slacey — Thu, 19 Sep 2024 20:32:49 GMT

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!

Re: DS2 Hash Package Output Overwrite

Tom — Fri, 20 Sep 2024 02:16:16 GMT

@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;

Re: DS2 Hash Package Output Overwrite

slacey — Fri, 20 Sep 2024 12:35:21 GMT

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.

Re: DS2 Hash Package Output Overwrite

yabwon — Fri, 20 Sep 2024 14:51:40 GMT

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

Re: DS2 Hash Package Output Overwrite

slacey — Fri, 20 Sep 2024 14:53:25 GMT

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