topic Re: PROC CONTENTS with DB2-Tables in SAS Programming

PROC CONTENTS with DB2-Tables

FK1 — Mon, 10 Sep 2018 13:15:26 GMT

Hello,

Quite a while ago, I encountered an issue that has been bothering me ever since. Namely, when I run a PROC CONTENTS on a DB2-Table that has been assigned in a library certain (meta) field information is missing:

Number of observations
Last modified date,
Create date
....

Here is a screenshot (the blackend out parts are due to confidentiality requirements)...

Has anybody an idea, why the metadata/dictionary tables are not filled, so that consequently the PROC CONTENTS procedure cannot retrieve any information?

Thx,

FK1

Re: PROC CONTENTS with DB2-Tables

SASKiwi — Mon, 10 Sep 2018 08:51:55 GMT

Your screenshot is typical of what PROC CONTENTS can display with external database tables. For example Dataset Type and Protection have no meaning when it comes to an external database. Also for performance reasons no observation count is shown. Behind the scenes that would require a - select count(*) - query to be run which could take a long time.

So in summary, PROC CONTENTS is showing details that are both relevant and efficient to extract for the requested table.

Re: PROC CONTENTS with DB2-Tables

FK1 — Mon, 10 Sep 2018 13:20:52 GMT

Hello @SASKiwi,

thank you for your remarks. I guess the sticking point to what it boils down is the question of "relevancy". For me personally I'd consider it rather relevant to know the # of observations (just like knowing the creation date) even though it could mean loss in efficiency/speed. But of course this is just my view when talking about DB2-Tables. Since PROC CONTENTS has to deal with ANY kindy of external DBMS, I guess, this is the lowest common denominator....

Re: PROC CONTENTS with DB2-Tables

SASKiwi — Mon, 10 Sep 2018 20:10:39 GMT

Hi @FK1 - I think your last sentence hits the nail on the head. BTW it isn't very difficult to build your own utility macro to count number of observations / rows in a table so it becomes relatively easy to do.

Re: PROC CONTENTS with DB2-Tables

FK1 — Tue, 11 Sep 2018 06:32:45 GMT

Hi @SASKiwi,

thank's for the hint with the utility macro. I'll just use this as a work around.

Cheers,

FK1