I have a SQL statement that I wish to automate using SAS EG (9.4). The following statement has been tested in Teradata SQL Assistant and works.
select * from TD.DATA where date='2015-06-01'
Now I wish to push this through a proc SQL pass through, and feed the date to the SQL program, like so....
proc sql;
connect to teradata as tera(user=&tera_user password="&tera_pwd" tdpid=terap);
create table MYDATA as
select * from connection to tera
(
select * from TD.DATA where date='2015-06-01'
);
disconnect from tera;
quit;
The above code has been tested and produces the exact same output as the previous SQL statement. However, what I really want is to do something like this:
%let input_date='2015-06-01';
proc sql;
connect to teradata as tera(user=&tera_user password="&tera_pwd" tdpid=terap);
create table MYDATA as
select * from connection to tera
(
select * from TD.DATA where date=&input_date.
);
disconnect from tera;
quit;
I have tried various combinations of quotations and different date formats....what am I missing here? Thanks.
When a calculation is performed on a column's value, its result can be stored, using :macro-variable, in the macro facility. The result can then be referenced by that name in another PROC SQL query or SAS procedure.
PROC SQL greatly increases SAS's flexibility in handling data, especially for multiple-table joining and database access. There are a number of comparisons between the DATA Step and the SQL procedure in SAS [1]. A majority of SAS functions can be directly used in the SQL procedure.
After a macro variable is created, you typically use the variable by referencing it with an ampersand preceding its name (&variable-name), which is called a macro variable reference. These references perform symbolic substitutions when they resolve to their value. You can use these references anywhere in a SAS program.
You can use the %BQUOTE()
macro function to resolve macro variables within single quotes.
%let input_date = 2015-06-01; proc sql; connect to teradata as tera(user=&tera_user password="&tera_pwd" tdpid=terap); create table MYDATA as select * from connection to tera ( select * from TD.DATA where date = %BQUOTE('&INPUT_DATE') ); disconnect from tera; quit;
Try this:
%let input_date=2015-06-01;
proc sql;
connect to teradata as tera(user=&tera_user password="&tera_pwd" tdpid=terap);
create table MYDATA as
select * from connection to tera
(
select * from TD.DATA where date=%str(%'&input_date%')
);
disconnect from tera;
quit;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With