topic Re: SAS Generate SQL Instructions in SAS Programming

SAS Generate SQL Instructions

foxrol94 — Thu, 18 Jan 2024 17:24:06 GMT

Hello,

I'd like to build a SAS macro programme to generates these SQL instructions on a single line.
Thanks for your help.

DELETE from LIB_ONE.TEMP_TABLE WHERE DAR='$DAR' AND VLD_ENR =1 AND SOURCE ='OP_Z' AND LET_OPE IN ('LEN','SELL') AND TIC_ENT IN ('107','102') AND ISIN IN ('BE00ZZZ', 'BE00YYYYYYY', 'BE001192XXX');

Re: SAS Generate SQL Instructions

PaigeMiller — Thu, 18 Jan 2024 17:32:57 GMT

@foxrol94 wrote:

I'd like to build a SAS macro programme to generates these SQL instructions on a single line.

Macros create dynamic code, that can change as needed without a human typing new code. What part of this code needs a macro?

Why does the code have to be on a single line?

Re: SAS Generate SQL Instructions

Tom — Thu, 18 Jan 2024 17:59:16 GMT

@foxrol94 wrote:

Hello,

I'd like to build a SAS macro programme to generates these SQL instructions on a single line.
Thanks for your help.
DELETE from LIB_ONE.TEMP_TABLE WHERE DAR='$DAR' AND VLD_ENR =1 AND SOURCE ='OP_Z' AND LET_OPE IN ('LEN','SELL') AND TIC_ENT IN ('107','102') AND ISIN IN ('BE00ZZZ', 'BE00YYYYYYY', 'BE001192XXX');

I suspect there is more to your actual problem.

But to generate that STATEMENT (lines don't have any importance to either the macro processor or the SQL language) you could just define the macro like this:

%macro mymacro;
DELETE from LIB_ONE.TEMP_TABLE WHERE DAR='$DAR' AND VLD_ENR =1 AND SOURCE ='OP_Z' AND LET_OPE IN ('LEN','SELL') AND TIC_ENT IN ('107','102') AND ISIN IN ('BE00ZZZ', 'BE00YYYYYYY', 'BE001192XXX');
%mend;

Then you could call the macro where ever you want the statement inserted.

proc sql;
 %mymacro
quit;

You might want to leave the semicolon out of the body of the macro and instead add it in the program that executes the macro.