topic Define local SQL-variable using explicit pass-through in SAS Data Management

Define local SQL-variable using explicit pass-through

user24feb — Mon, 28 May 2018 08:05:32 GMT

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

Re: Define local SQL-variable using explicit pass-through

Kurt_Bremser — Mon, 28 May 2018 08:41:50 GMT

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.

Re: Define local SQL-variable using explicit pass-through

user24feb — Mon, 28 May 2018 08:48:30 GMT

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.

Re: Define local SQL-variable using explicit pass-through

Kurt_Bremser — Mon, 28 May 2018 08:58:57 GMT

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.

Re: Define local SQL-variable using explicit pass-through

Tom — Mon, 28 May 2018 17:40:03 GMT

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

Re: Define local SQL-variable using explicit pass-through

user24feb — Tue, 29 May 2018 06:39:54 GMT

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.