topic Re: How to print table name using proc fedsql in SAS Enterprise Guide

How to print table name using proc fedsql

_el_doredo — Mon, 12 Aug 2024 07:59:31 GMT

Hello Experts,
I am having a table which has more than 10billion records. By using PROC SQL, it prints the count in exponential value.
Due to that, I am using PROC FEDSQL to print the values. However, I want to print table name as well in my output. But, PROC FEDSQL considering hardcoded value as a column and searching it against the table and erroring by saying “Column not found”. Please help me to resolve this error.

I have added the code below

%LET TABLE_NAME=Sales;
PROC FEDSQL;
CREATE TABLE WANT AS
SELECT “&TABLE_NAME.” as Table_Name,
CAST(COUNT(*) AS BIGINT) as Count
From Sales;
QUIT;

I am fetching count for many tables so I can’t hardcode table name directly. If I hardcode directly it’s working fine. But while resolving the hardcoded value in macro variable it’s considering it as a variable

Re: How to print table name using proc fedsql

Kurt_Bremser — Mon, 12 Aug 2024 09:43:45 GMT

%let table_name = SALES; /* use uppercase! */

proc sql;
create table want as
  select
    memname as table_name,
    nobs as count format=15. /* increase format if needed */
  from dictionary.tables
  where libname = "WORK" and memname = "&table_name."
;
quit;

Query the metadata instead of reading the whole dataset.

PS to do this for several datasets, create a dataset with libnames and memnames (all uppercase) and do a join with DICTIONARY.TABLES.

Re: How to print table name using proc fedsql

Tom — Mon, 12 Aug 2024 12:26:23 GMT

I suspect that PROC FEDSQL is treating "XXX" as a NAME instead of STRING LITERAL. Just like PROC SQL would if you use the DQUOTE=ANSI option.

Try using single quotes.

%let TABLE_NAME=Sales;
proc fedsql;
create table WANT as
  select %unquote(%bquote('&TABLE_NAME')) as Table_Name
       , CAST(COUNT(*) AS BIGINT) as Count
  from &table_name
;
quit;

Note that trying to store an actual BIGINT value into a SAS numeric variable might cause trouble. The maximum integer that SAS can store exactly is 9,007,199,254,740,992.

1    data _null_;
2      maxint=constant('exactint');
3      put maxint= comma32.;
4    run;

maxint=9,007,199,254,740,992

Re: How to print table name using proc fedsql

AhmedAl_Attar — Mon, 12 Aug 2024 13:25:45 GMT

@_el_doredo

To use macro variable in Proc FedSQL, you'll need to use the SAS macro function %TSLIT.

Check this on-line doc for reference SAS Help Center: Macro Variables

Re: How to print table name using proc fedsql

_el_doredo — Mon, 12 Aug 2024 13:37:38 GMT

Hi all,

Thanks for your response. All responses are working fine and another response I found is mentioning table name in single quotes also resolving this issue.

%LET TABLE_NAME=‘Sales’;
PROC FEDSQL;
CREATE TABLE WANT AS
SELECT “&TABLE_NAME.” as Table_Name,
CAST(COUNT(*) AS BIGINT) as Count
From Sales;
QUIT;