@Ashpak wrote:
data _null_;
x=today();
y=x;
format y date9.;
run;
I am trying to get the date from date function and storing this variable into Y and calling this variable in ODBC select SQL
RROR: CLI describe error: [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name '&y'. : [Microsoft][SQL Server
That code it putting the same value into the dataset variables X and Y, but asking it display Y using the DATE format and letting X be displayed as just the raw number of days since 1960. But since it is a DATA _NULL_ step both X and Y disappear when the step is over.
If you want to refence a macro variable named Y then you have to create a macro variable named Y. If you want to use the value of the macro variable in the code you generate to run in your remote database then you need to know what type of value your remote database expects at that place in the code. So if the macro variable Y is supposed to represent a date then you need know how that database expects you to represent a date. You might try this syntax:
data _null_;
call symputx('today',quote(put(today(),yymmdd10.),"'"));
run;
....
proc sql ;
connect to ... as X ... ;
create table want as
select * from connection to X
(select * from myschema.mytable where myvar = DATE &today)
;
quit;