topic Proc Contents on a "Connect To SQL Server" in SAS Programming

Proc Contents on a "Connect To SQL Server"

b77 — Tue, 08 Aug 2023 20:58:38 GMT

I run proc sql by connecting to sql server table (proc sql; connect to sqlsvr (dsn=table_name readbuff=15000))

I would like to run a proc contents on the table (dsn=table_name) but I must be getting the syntax wrong. Is it even possible to run proc contents when connecting to SQL server?

Any help would be appreciated...

Re: Proc Contents on a "Connect To SQL Server"

Quentin — Tue, 08 Aug 2023 22:24:14 GMT

When you use PROC SQL to connect to SQL server, you're using "explicit pass-through" to let you write Microsoft SQL Server code and send it to SQL server to execute.

You cannot use PROC CONTENTS in an explicit pass-through block because PROC CONTENTS is SAS code, not SQL Server Code.

If you want to run PROC CONTENTS on a SQL server table, you can use the LIBNAME statement to create a libref that points to the SQL server database. Then you can run the usual PROC CONTENTS step:

libname mysqldb ... ;
proc contents data=mysqldb.mytable;
run;

When you do that, you're using "implicit pass-through". You're submitting SAS code and telling SAS to translate it into SQL server code.

Re: Proc Contents on a "Connect To SQL Server"

Patrick — Wed, 09 Aug 2023 04:35:18 GMT

On top of what @Quentin says:

Proc Contents then will you show how the SQL Server columns map into SAS columns. It will not show you the SQL Server column types (like: a varchar on the SQL Server side will map into a CHAR on the SAS side and that's what Proc Contents will show).