topic Re: Execute Conditional SQL in SAS Programming

Execute Conditional SQL

fabiopjr — Wed, 30 Jun 2021 18:26:40 GMT

Hi,

I need to execute a SQL only if the name of a file contains the expression y0.

I tried it:

LIBNAME RECEITA BASE "G:\RECEITA";
IF instr(&Arq,"Y0.")>0 then call execute(
	"Proc sql;
	DELETE FROM RECEITA.ESTABELECIMENTOS;")
	RUN;

But generats error:

How can I solve this?

Re: Execute Conditional SQL

PaigeMiller — Wed, 30 Jun 2021 19:04:33 GMT

Your IF statement must be in a DATA step. It will not work otherwise.

But you still have other problems preventing this from working. If your logic is to find the value "Y0." (that's three characters, a capital Y, a zero and a dot, in sequence) somewhere in variable &arq (but you haven't defined &arq), then you still have a problem that there is no INSTR function.

So, please, describe in words (not SAS code) exactly (and I mean in detail, step-by-step, leaving nothing out) what you are trying to do.

Re: Execute Conditional SQL

fabiopjr — Thu, 01 Jul 2021 19:39:11 GMT

I need to use the "Replace" method when I'm going to read the first file, and the "Append to Existing" method in the others.

I had the idea to use "Append to Existing" and delete the table when I detected the reading of the first file. the code worked, however it had a problem with the table.

%macro APAGA;
/*proc sql;
	DELETE FROM RECEITA.ESTABELECIMENTOS;
	run;*/
	data RECEITA.ESTABELECIMENTOS;
	set RECEITA.ESTABELECIMENTOS (obs=0);
	run;
	
quit;

data _null_;
	IF instr(&Arq,"Y0.")>0 then call execute('%APAGA');
run;
quit;

I managed to solve it in another way, now I have two Jobs, one with the Append option and the other with Replace, I choose between one and the other in a third job.

My solution: