Passing macro variable in PROC SQL for ODBC data set

[Ashpak]

Hi  I would like to select macro value along with ODBC data set columns

proc sql;

connect to odbc(DSN=&DSN user=&user password=&pwds);

create table TABLEY as select * from connection to odbc

(

select &y as dates,* from TABLE X ;

);

RUN;

[Reeza]

What's in macro variable &Y?

Essentially your code needs to resolve to valid SAS code so take whatever is in Y and literally put it into your code. If that doesn't run, it's not valid.

select "ABC" as dates, * from table X that will work.

Or

select "01JAN2019"D as dates will also work.

Select 2021-02-23 as DATES will probably work but not what you expect since 2021-2 = 2019 - 23 = 1998 as Dates

[Ashpak]

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

[Tom · Accepted solution]

---

@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;