SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Define local SQL-variable using explicit pass-through

Accepted Solution Solved
Reply
Super Contributor
Posts: 353
Accepted Solution

Define local SQL-variable using explicit pass-through

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


Accepted Solutions
Solution
3 weeks ago
Super User
Posts: 9,922

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

[ Edited ]
Posted in reply to user24feb

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code

View solution in original post


All Replies
Super User
Posts: 9,922

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

Posted in reply to user24feb

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super Contributor
Posts: 353

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

Posted in reply to KurtBremser

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.

Solution
3 weeks ago
Super User
Posts: 9,922

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

[ Edited ]
Posted in reply to user24feb

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super User
Super User
Posts: 7,938

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

Posted in reply to user24feb

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 ...;
Super Contributor
Posts: 353

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

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.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 144 views
  • 3 likes
  • 3 in conversation