Search code examples
postgresqlsasinformation-schema

postgresql information_schema exceeds 8 characters

In my new work they use SAS and I need to get a list of columns names.

In order to accomplish this, I want to use information_schema in postgresql from SAS. However, SAS has a libname constraint of 8 characters.

Here is my code to get the column names of a dataset:

proc sql;
CREATE TABLE table_2 AS
SELECT
*
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_NAME='table_1';
run;

this results in the following error:

ERROR: libname "information_schema" exceeds 8 characters

Has anyone been able to find a solution for this problem?

Thank you

Solution

  • To get the list of variables in a SAS dataset use PROC CONTENTS. Example:

    proc contents data=sashelp.class;
run;

    If you have a libref pointed at a POSTGRESQL database then you should be able to do the same thing. So if you defined the libref POSTGRES to point at the database schema that has a dataset (aka "table") named TABLE_1 then code is just:

    proc contents data=POSTGRES.table_1 ;
run;

    If you really want to write POSTGRESQL code then use pass-thru SQL syntax. Again if you have defined a libref named POSTGRES that is pointing to the database your SQL might look like:

    proc sql;
 connect using POSTGRES;
 select * from connection to POSTGRES 
   (select * from INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME='table_1'
   )
 ;
quit;