It appears to be a bug on the TABLE_CATALOG field in dictionary.TABLE_CONSTRAINTS
proc sql;
create table work.example(
TX_FROM float format=datetime19.,
DD_TYPE char(16),
DD_SOURCE char(2048),
DD_SHORTDESC char(256),
constraint pk primary key(tx_from, dd_type,dd_source),
constraint unq unique(tx_from, dd_type),
constraint nnn not null(DD_SHORTDESC)
);
/* this table has no records */
create table work.zero_records as
select * from dictionary.TABLE_CONSTRAINTS
where upcase(TABLE_CATALOG)="WORK";
/* this table has expected records */
create table work.has_records as
select * from dictionary.TABLE_CONSTRAINTS
where TABLE_CATALOG="WORK";
The behaviour is exhibited with other functions as well. The behaviour does not apply to other columns (eg TABLE_NAME).
SYSVLONG=9.04.01M7P080520
Indeed.
These methods would work
create table work.has_records1 as
select * ,upcase(TABLE_CATALOG) as test from sashelp.vtabcon
where calculated test eq upper("WoRK");
create table work.has_records2 as
select * from (select *,TABLE_CATALOG as TABLE_CATALOG1 from sashelp.vtabcon)
where upcase(TABLE_CATALOG1) eq 'WORK';
But not these ones either:
create table work.zero_records1 as
select * from sashelp.vtabcon
where upper(TABLE_CATALOG) eq 'WORK';
/* this table has no records */
create table work.zero_records2 as
select * from dictionary.TABLE_CONSTRAINTS
where prxmatch('/work/oi',strip(TABLE_CATALOG));
create table work.zero_records3 as
select * from sashelp.vtabcon
where strip(lower(TABLE_CATALOG)) eq 'work';
- Cheers -
Indeed.
These methods would work
create table work.has_records1 as
select * ,upcase(TABLE_CATALOG) as test from sashelp.vtabcon
where calculated test eq upper("WoRK");
create table work.has_records2 as
select * from (select *,TABLE_CATALOG as TABLE_CATALOG1 from sashelp.vtabcon)
where upcase(TABLE_CATALOG1) eq 'WORK';
But not these ones either:
create table work.zero_records1 as
select * from sashelp.vtabcon
where upper(TABLE_CATALOG) eq 'WORK';
/* this table has no records */
create table work.zero_records2 as
select * from dictionary.TABLE_CONSTRAINTS
where prxmatch('/work/oi',strip(TABLE_CATALOG));
create table work.zero_records3 as
select * from sashelp.vtabcon
where strip(lower(TABLE_CATALOG)) eq 'work';
- Cheers -
Thanks @Oligolas - indeed, that was the approach I used to fix my issue: https://github.com/sasjs/core/pull/84/files#diff-60df2277f57ab4c7bbd043138958de8dc28e6ccddfddb9e8501...
Interesting to see that the problem applies to sashelp.vtabcon also. And nice idea with the subquery.
Also credit to @yabwon who investigated behind the scenes, which resulted in a vastly refined / simpler question.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.