topic Re: Problems with ODBC connection (var names) in SAS Programming

Problems with ODBC connection (var names)

rubence — Wed, 08 May 2024 09:34:49 GMT

Hi,

I have the following ODBC connection:

LIBNAME IFRS17_H ODBC DATAsrc=IFRS17_HIVE SCHEMA=ifrs17catalog USER="rubence" PASSWORD="XXXXXXXXXXX";

When I try to use a WHERE clause,

data KFS;
set IFRS17_H.key_figures;
where key_figures.pyear='2024';
run;

SAS shows the following message:

but if I do not use a WHERE clause, I have no problems and the var names in the new table follows the structure: TABLE_NAME.VAR_NAME.

My questions are:

a) How can I avoid to download the full table?

b) Is It possible to use only the VAR_NAME and not TABLE_NAME.VAR_NAME?

Many thanks for your great help.

Re: Problems with ODBC connection (var names)

Patrick — Wed, 08 May 2024 09:54:55 GMT

SAS data step syntax doesn't require (and doesn't support) two level names for variables.

data KFS;
set IFRS17_H.key_figures;
where pyear='2024';
run;

In your syntax you expect variable pyear to be of type character. Should this not be the case (=it's numeric) then get rid of the quotes. ...and should this still not return the expected result then try year(pyear)=2024

Re: Problems with ODBC connection (var names)

ballardw — Wed, 08 May 2024 13:53:24 GMT

A hint on how to read SAS error messages.

When you get a section of code underlined, as in this example, SAS is telling you where it found a problem. Sometimes the actual problem may be a missing semicolon on a previous line making the current line incorrect but that usually shows the underline at the start of the offending line(s).

So this message relates to the entire key_figures.pyear. A very little research of code referencing variables will show that generally there is no two level name used in the data step. When it does occur it relates to specific types of uses such as First. Last. (used with BY group variables), hash keys and iterators and a few other elements of the Component Objects related to logging and Java.

@rubence wrote:

Hi,

I have the following ODBC connection:
LIBNAME IFRS17_H ODBC DATAsrc=IFRS17_HIVE SCHEMA=ifrs17catalog USER="rubence" PASSWORD="XXXXXXXXXXX";
When I try to use a WHERE clause,
data KFS;
set IFRS17_H.key_figures;
where key_figures.pyear='2024';
run;
SAS shows the following message:

but if I do not use a WHERE clause, I have no problems and the var names in the new table follows the structure: TABLE_NAME.VAR_NAME.

My questions are:

a) How can I avoid to download the full table?

b) Is It possible to use only the VAR_NAME and not TABLE_NAME.VAR_NAME?

Many thanks for your great help.

Re: Problems with ODBC connection (var names)

Tom — Wed, 08 May 2024 13:55:15 GMT

If you have the VALIDVARNAME option set to ANY then you can reference a non-standard variable name by using a NAME LITERAL. They work similar to DATE LITERALS. You add quotes around the name and append the letter N.

where "key_figures.pyear"n='2024';

Re: Problems with ODBC connection (var names)

rubence — Thu, 09 May 2024 08:58:15 GMT

I have tried your solution but SAS shows a new error:

Re: Problems with ODBC connection (var names)

Patrick — Thu, 09 May 2024 09:22:57 GMT

@rubence Use a ONE LEVEL NAME. This is SAS data step and not SQL syntax.

data KFS;
set IFRS17_H.key_figures;
where ~~key_figures.~~pyear='2024';
run;

...or if you're more comfortable with SQL then use SQL.

proc sql;
  create table work.KFS as
  select *
  from IFRS17_H.key_figures
  where key_figures.pyear='2024'
  ;
quit;

The 2nd error message you get show that the SAS data step code gets converted into the DB SQL flavour that then gets sent to the DB for processing (so the where clause could subset the data on the DB side before transferring the data to SAS). ...but because in your code the data step syntax is wrong things ain't working.

If you add below options to your code then you'll get in the SAS log the info which SQL has been sent to the DB for in-database processing.

options  sastrace=(,,,d) sastraceloc=saslog nostsuffix;