Parametrize values in proc sql queries

[KJazem]

Hi.

I have a SAS program that runs many SQL queries from Teradata and from in-memory data in SAS. For Teradata, I connect using server information, read the relevant tables from a schema, and output the results into SAS. 

proc sql noprint;
    connect using TD;
    create table LIB.TEMP as select * from connection to TD;
    (select * from SCHEMA.TEMP where date_value = '2022-01-01');
    disconnect from TD;
quit;

I want to make date_value inside the query 'dynamic' or as a parameter. What would be the best way to do this?

Is it using variables/parameters declared at the top of the program - but can they be used inside a SQL statement? This technically connects to Teradata so not sure if will recognize the variable, defined outside the select statement. Similarly for the base SAS SQL procs.

How can I extend this to make it more dynamic? So for example, I want the parameter to be monthly, so after '2022-01-01', it automatically assigns the next month, '2022-02-01', then reruns the program.

[DavePrinsloo]

Use macro variables,

eg 

%let query_date =  '2022-01-01';
proc sql noprint;
    connect using TD;
    create table LIB.TEMP as select * from connection to TD;
    (select * from SCHEMA.TEMP where date_value = &query_date.);
    disconnect from TD;
quit;

This means you add all your dynamic parts at the beginning of your code.   

The solution above is explain the concept but and does help you prevent changing the code with each run.    

To do that you can use a couple of different mechanisms

1. create an additional sas program that contains statements like the %let statement that can be used to parametrize all you programs, and then %include this new program in all programs that need parameterization.  You then only need to change a single file before each run.
2. You can use the sysget function to initializes the macro variable query_date from an external system variable that may be set  by a scheduler.
3.  You can derive the value of query_date using a function INTNX('YEAR',today(),0)  and then use a put function with the appropriate date format to set the macro variable using call symputx.
4. You can combine the above concepts, i.e. create a file to include in all your programs - but set the macro variables dynamically where possible. 

[KJazem]

Thank you for the reply.

For the second point, can you please direct me to a reference or show me an example? Can't seem to get exactly what I want. 

For the third point, would something like this work? This will create the query_date macro. Not sure how I would then convert that into the format I need, which is 2022-01-01, so yyyy-mm-dd. 

data _null_;
    call symputx('query_date', intnx('year', today(), 0));
run;

[Tom]

Format the text you put into the macro variable.

data _null_;
    call symputx('query_date', quote(put(intnx('year', today(), 0),yymmdd10.),"'"));
run;

[Reeza]

---

@KJazem wrote:

Thank you for the reply.

For the second point, can you please direct me to a reference or show me an example? Can't seem to get exactly what I want. 

For the third point, would something like this work? This will create the query_date macro. Not sure how I would then convert that into the format I need, which is 2022-01-01, so yyyy-mm-dd. 

data _null_;
    call symputx('query_date', quote(put(intnx('year', today(), 0, 'b'), yymmddd10.), "'"));
run;

---

Add the b parameter so that you have the beginning of the month, ie 01 and use PUT() to specify the format. You also want it in quotes, so you use the quote function. See attempt above, not 100% sure I got the paranthesis correct but the idea is there.