BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
myelemes
Fluorite | Level 6

Hi,

 

I am trying to use PROC FEDSQL in SAS EG 7.1 but it is not working for me.

Does anyone know what is going on here? 

 

Error messages I am seeing are as below: 

28         proc fedsql;

ERROR: Duplicate catalog name, SYSTEM, encountered in connection string, DSN or file DSN

ERROR: PROC FEDSQL initialization failed.

29         select count(*) from cad.IP_ACCT_XREF_ME_V;

30         quit;

 

SAS code I am trying to run:

 

 

Libname cad2 sybaseiq dsn="&mbaDB" user="&officeUser" password="&officePwd";

proc fedsql;select count(*) from cad2.IP_ACCT_XREF_ME_V; 
quit;
run;

 

1 ACCEPTED SOLUTION

Accepted Solutions
ChrisHemedinger
Community Manager

It still seems like you're comparing a datetime value (mba_run_dtm), which is represented as a number, to a string literal.

 

In database-specific SQL, that might work.  But I'm not sure that the literal value in FEDSQL is converted to a legit datetime for comparison.  It feels like you still need to do something to convert that '2018-05-11 19:51:09.744' value to native value.  Maybe you need to add the TIMESTAMP keyword:

 

proc fedsql  libs=(mba mbadsa);
SELECT count(*) from mba.dsa.equifax_das_scr where 
 mba_run_dtm > TIMESTAMP&equifax_data_date;
quit;

(Not an expert on FEDQL here...but learning...)

 

 

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.

View solution in original post

7 REPLIES 7
ChrisHemedinger
Community Manager

This is caused by duplicate names among the native catalogs in the database you're accessing.  Plays up when you have multiple libname connections.

 

I didn't find a SAS note but I did find a tech support track with some general advice.  I'll include that here, but if you need help making sense of it or have further issues, I suggest contacting Tech Support.

If using Proc FedSQL or Proc DS2 when multiple Libname connections have been assigned to a database that supports native

catalogs, this error might occur.

 

  ERROR: Duplicate catalog name, <name>, encountered in connection string, DSN or file DSN

 

Native catalogs are automatically included in the connection and are not listed in the CATALOG= parameters.  To prevent the

errors you will need to specify all additional native catalog entries in the connection string and you will need to rename any that

produce errors.  

 

For example, the following Netezza server has two native catalogs named MODEL and SYSTEM.  An error will occur for each

duplicate catalog name found.  The error seen in this case was:

 

  ERROR: Duplicate catalog name, MODEL, encountered in connection string, DSN or file DSN

 

The example below shows how the two catalog names are renamed to prevent the errors.  

 

To write the connection strings for all of the Libnames currently assigned to the SAS session to the SAS Log add this option

prior to the Proc DS2; or Proc FedSQL; statement:

 

  options msglevel=i ;

 

The connection strings below illustrate how to rename the catalog names to prevent the errors caused by duplicate names.

 

 

  proc fedsql nolibs conn="DRIVER=FEDSQL;CONOPTS=(
                    (DRIVER=NETEZZA;SERVER=daily;DATABASE=TEST;UID=net_test;PWD=*;
                       CATALOG=(X1=TEST;X1_MODEL=MODEL;X1_SYSTEM=SYSTEM;));
                    (DRIVER=NETEZZA;SERVER=daily;DATABASE=system;UID=net_test;PWD=*;
                        CATALOG=(X2=TEST2;X2_MODEL=MODEL;X2_SYSTEM=SYSTEM;));)";
    select * from X1.customer;
  quit;

 

To turn prevent the connection strings from being written to the SAS Log add this option:

 

  options msglevel=n; 

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.
myelemes
Fluorite | Level 6

Thank you, I did try renaming the catalog, as suggested below, but a new error message kept saying that Catalog arguments are bad.

 

 

proc fedsql  libs=(mba);

WHen I used libs= option, it seems to work.

 

 

My new issue is that I am having difficulty resolving Macro variables in Prod FedSQl;

 

 

proc fedsql  libs=(mba mbadsa);
SELECT count(*) from mba.dsa.equifax_das_scr where mba_run_dtm > &equifax_data_date;
quit;

run;

Above gives me following error:

 

 

37         proc fedsql  libs=(mba mbadsa);
NOTE: WARNING: [DataDirect][ODBC lib] Application's WCHAR type must be UTF16, because odbc driver's unicode type is UTF16
NOTE: WARNING: [DataDirect][ODBC lib] Application's WCHAR type must be UTF16, because odbc driver's unicode type is UTF16
38         SELECT count(*) from mba.dsa.equifax_das_scr where mba_run_dtm > &equifax_data_date;
ERROR: Error occurred building the Execution Tree.
ERROR: Error occurred initiating Filter.
ERROR: General error
NOTE: PROC FEDSQL has set option NOEXEC and will continue to prepare statements.
39         quit;

 

 

When I surround macro variable with double quotes, it resolves, but I don't need the double quotes to stay in the output... Also, sas thinks it is a column name... 

 

37         proc fedsql  libs=(mba mbadsa);
NOTE: WARNING: [DataDirect][ODBC lib] Application's WCHAR type must be UTF16, because odbc driver's unicode type is UTF16
NOTE: WARNING: [DataDirect][ODBC lib] Application's WCHAR type must be UTF16, because odbc driver's unicode type is UTF16
38         SELECT count(*) from mba.dsa.equifax_das_scr where mba_run_dtm > "&equifax_data_date";
ERROR: Column "'2018-05-11 19:51:09.744'" not found or cannot be accessed
NOTE: PROC FEDSQL has set option NOEXEC and will continue to prepare statements.
39         quit;

 

 

 

 

 

ChrisHemedinger
Community Manager

If you really need to compare against a literal value in single quotes, use the %TSLIT macro to resolve.

 

%let equifax_data_date= 2018-05-11 19:51:09.744;
%put %tslit(&equifax_data_date);

Result:

'2018-05-11 19:51:09.744'

 

So your case would be something like:

 

proc fedsql  libs=(mba mbadsa);
SELECT count(*) from mba.dsa.equifax_das_scr where mba_run_dtm > %tslit(&equifax_data_date);
quit;

But I suspect your comparison needs to use a datetime value, in which case you might need to do something like:

 

data _null_;
 x = "11May2018:19:51:09.744"dt;
 put x=;
 call symputx('equifax_data_date',x);
run;
It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.
myelemes
Fluorite | Level 6

I tried both approaches but proc fedsql just does not understand that I am passing in a macro variable.

 

50         proc fedsql libs=(mbadsa);
51         SELECT
52         count(*)
53         FROM
54             MBADSA.DSA.EQUIFAX_DAS_SCR
55         where
56         MBA_run_dtm >  %tslit(&equifax_data_date);
ERROR: Error occurred building the Execution Tree.
ERROR: Error occurred initiating Filter.
ERROR: General error
NOTE: PROC FEDSQL has set option NOEXEC and will continue to prepare statements.
57         quit;

 

 

ChrisHemedinger
Community Manager

Can you show how you set the value of that macro variable, and %PUT the raw value so we can see what you're passing in?

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.
myelemes
Fluorite | Level 6

I receive a timestamp from a macro, I am pulling the latest timestamp value from a netezza table:

 

PROC SQL NOPRINT;
connect to netezza (&fwstAIConnect);
SELECT catt("'",(cat(put(datepart(lst_mba_run_dtm),yymmdd10.),' ',put(timepart(lst_mba_run_dtm),time12.3))),"'") INTO :&TIME_VALUE_VAR
FROM CONNECTION TO netezza
(
SELECT nvl(max(mba_run_dtm), '1900-01-01') AS lst_mba_run_dtm 
from &table_name
;
);
DISCONNECT FROM netezza;
QUIT;

&equifax_data_date - is the date I receive frm the macro, please see below the %PUT statement.

 

33 %let equifax_data_date = &equifax_data_date;
34 %put equifax_data_date .............................. &equifax_data_date;
equifax_data_date .............................. '2018-05-11 19:51:09.744'
ChrisHemedinger
Community Manager

It still seems like you're comparing a datetime value (mba_run_dtm), which is represented as a number, to a string literal.

 

In database-specific SQL, that might work.  But I'm not sure that the literal value in FEDSQL is converted to a legit datetime for comparison.  It feels like you still need to do something to convert that '2018-05-11 19:51:09.744' value to native value.  Maybe you need to add the TIMESTAMP keyword:

 

proc fedsql  libs=(mba mbadsa);
SELECT count(*) from mba.dsa.equifax_das_scr where 
 mba_run_dtm > TIMESTAMP&equifax_data_date;
quit;

(Not an expert on FEDQL here...but learning...)

 

 

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 7 replies
  • 3688 views
  • 1 like
  • 2 in conversation