Define local SQL-variable using explicit pass-through

[user24feb]

Hello,

is it possible (and how) to define a local SQL variable using ODBC-explicit-pass-through? I mean something like this:

Proc SQL;
  Connect To ODBC (DSN=<Source>);
  Create Table xx As 
  Select *
  From Connection To ODBC
  (
  Declare @Max_KeyDate

  Select @Max_KeyDate = Max(KeyDate) From <view>

  Select * From <view> Where Key_Date = @Max_KeyDate
  );
  Disconnect from ODBC;
Quit;

Thx & kind regards

[Kurt_Bremser]

Store in a macro variable:

Proc SQL;
  Connect To ODBC (DSN=<Source>);
  Create Table xx As 
  Select max_key_date into :max_key_date
  From Connection To ODBC
  (
  Select Max(KeyDate) as max_key_date From <view>
  );
  Disconnect from ODBC;
Quit;

After that, use &max_key_date where needed. Be aware that you might have to do a conversion so the SAS macro variable holds the correct numerical value for dates and datetimes, as the different RDBMS systems have different ways of storing, handling and displaying such values.

[user24feb]

Yes, a macro var is an alternative, but in this case leads to an %unquote(%str( ..)). I only would like to know, if it's possible and what's the effort.

[Kurt_Bremser · Accepted solution]

A macro var is not an alternative, it is the only solution. SAS does not have SQL variables (and the declare statement) that you are used to. In SAS you use macro variables.

Format the date in the remote SQL to something you can easily use in SAS (eg, first convert to a YYYY-MM-DD string, and then take the max() of that). After that, do a quick conversion to a SAS date value:

%let max_key_date=2018-05-28; /* this is what you should get from the remote SQL call */

%let max_key_date=%sysfunc(inputn(&max_key_date,yymmdd10.));

%put &max_key_date;

data _null_;
td = today();
put td=;
run;

the data step is in there to show you that the conversion in the %sysfunc works.

[Tom]

Did you try running those three remote statements separately?

connect ... ;
execute (Declare @Max_KeyDate) by ODBC;
execute (Select @Max_KeyDate = Max(KeyDate) From <view>) by ODBC;
create table xx as 
  select *
  from connection to ODBC
  ( 
  Select * From <view> Where Key_Date = @Max_KeyDate
  );
disconnect ...;

[user24feb]

When I combine execute (declare @MaxDate select @MaxDate = ..) it seems to work, at least I am not getting an error message. However, the local variable seems to be erased between the statements, so I cannot use it in the create-table-from-connection-statement. It seems it is something I just should not do.