BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
AndreaMacLeod
Calcite | Level 5

I am trying to use the previously posted Solution from 'PROC CONTENTS of entire library with all variables detail': https://communities.sas.com/t5/SAS-Procedures/PROC-CONTENTS-of-entire-library-with-all-variables-det... without success.

 

Am I missing a step or required setting to the recommended code?

 

First code/log:

 

LIBNAME dawn SQLSVR datasrc=SQL404 user="&dawn_user" pw="&dawn_pw";

proc sql;
create table columns as
select name as variable
,memname as table_name
from dictionary.columns
where libname = 'DAWN'
;
quit;

NOTE: Libref DAWN was successfully assigned as follows:
Engine: SQLSVR
Physical Name: SQL404
28
29 /*_______________________________________________________*/
30
31 proc sql;
32 create table columns as
33 select name as variable
34 ,memname as table_name
35 from dictionary.columns
36 where libname = 'DAWN'
37 ;
NOTE: Table WORK.COLUMNS created, with 0 rows and 2 columns.
38 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.06 seconds
cpu time 0.03 seconds

 

Second code/log:

PROC DATASETS LIB = DAWN;
CONTENTS DATA =_all_; 
QUIT; 
RUN;

27 PROC DATASETS LIB = DAWN;
WARNING: No matching members in directory.
28 CONTENTS DATA =_all_;
29 QUIT;

WARNING: No matching members in directory.
NOTE: PROCEDURE DATASETS used (Total process time):
real time 0.06 seconds
cpu time 0.00 seconds

30 RUN;
31
32 GOPTIONS NOACCESSIBLE;
33 %LET _CLIENTTASKLABEL=;
34 %LET _CLIENTPROCESSFLOWNAME=;
35 %LET _CLIENTPROJECTPATH=;
36 %LET _CLIENTPROJECTPATHHOST=;
37 %LET _CLIENTPROJECTNAME=;
38 %LET _SASPROGRAMFILE=;
39 %LET _SASPROGRAMFILEHOST=;
40
41 ;*';*";*/;quit;run;
42 ODS _ALL_ CLOSE;
43
44
45 QUIT; RUN;

 

I'm on SAS 9.4M3 using Grid and I can successfully run a standard PROC CONTENTS on a single table in the library, so its not a connection issue. 

proc contents data=dawn.Tbl_Change;
run;

25 GOPTIONS ACCESSIBLE;
26 proc contents data=dawn.Tbl_Change;
27 run;

NOTE: PROCEDURE CONTENTS used (Total process time):
real time 0.14 seconds
cpu time 0.04 seconds

28
29 GOPTIONS NOACCESSIBLE;
30 %LET _CLIENTTASKLABEL=;
31 %LET _CLIENTPROCESSFLOWNAME=;
32 %LET _CLIENTPROJECTPATH=;
33 %LET _CLIENTPROJECTPATHHOST=;
34 %LET _CLIENTPROJECTNAME=;
35 %LET _SASPROGRAMFILE=;
36 %LET _SASPROGRAMFILEHOST=;
37
38 ;*';*";*/;quit;run;
39 ODS _ALL_ CLOSE;
40
41
42 QUIT; RUN;

 

Data Set NameDAWN.Tbl_ChangeObservations.
Member TypeDATAVariables10
EngineSQLSVRIndexes0
Created.Observation Length0
Last Modified.Deleted Observations0
Protection CompressedNO
Data Set Type SortedNO
Label   
Data RepresentationDefault  
EncodingDefault  

 

Alphabetic List of Variables and Attributes
#VariableTypeLenFormatInformatLabel
5ChangeDateTimeNum8DATETIME22.3DATETIME22.3ChangeDateTime
1ChangeIDNum81111ChangeID
4FieldNameChar50$50.00$50.00FieldName
9ForeignKeyFromValueNum81111ForeignKeyFromValue
10ForeignKeyToValueNum81111ForeignKeyToValue
6FromValueChar255$255.00$255.00FromValue
3RecordIDFKNum81111RecordIDFK
2TableNameChar50$50.00$50.00TableName
7ToValueChar255$255.00$255.00ToValue
8UserNameChar50$50.00$50.00UserName

 

 

Any suggestions or ideas on a solution?

 

Thanks

 

Andrea

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

Data on a server isn't stored in the dictionary tables, sadly 😞

 

Most servers have their own versions so if you can do a pass through query you may be able to access the information directly from the server. 

View solution in original post

2 REPLIES 2
Reeza
Super User

Data on a server isn't stored in the dictionary tables, sadly 😞

 

Most servers have their own versions so if you can do a pass through query you may be able to access the information directly from the server. 

AndreaMacLeod
Calcite | Level 5

Hi Reeza - thank you for your quick response. Once I thought about it - your answer made perfect sense.

 

Sorry for the delay in replying and thanking you, but I decided to pursue the pass through query first so I could post a solution as well.

 

Here's what my Data Analyst, Doug Char came up with:

PROC SQL;
CONNECT TO SQLSVR (DATAsrc=SQL404 USER="&dawn_user" PW="&dawn_pw");
CREATE TABLE tbl_names AS
SELECT * FROM CONNECTION TO SQLSVR
(SELECT table_name FROM information_schema.tables WHERE table_type='BASE TABLE');
DISCONNECT FROM SQLSVR;

PROC SQL;
CONNECT TO SQLSVR (DATAsrc=SQL404 USER="&dawn_user" PW="&dawn_pw");
CREATE TABLE all_data AS
SELECT * FROM CONNECTION TO SQLSVR
(SELECT OBJECT_SCHEMA_NAME(t.[object_id], db_id()) AS [Schema],
t.[name] AS [table_name], ac.[name] AS [column_name],
ty.[name] AS system_data_type, ac.[max_length],
ac.[precision], ac.[scale], ac.[is_nullable], ac.[is_ansi_padded]
FROM sys.[tables] AS t
INNER JOIN sys.[all_columns] ac
ON t.[object_id] = ac.[object_id]
INNER JOIN sys.[types] ty
ON ac.[system_type_id] = ty.[system_type_id] AND ac.[user_type_id] = ty.[user_type_id]
WHERE t.[is_ms_shipped] = 0
ORDER BY t.[name], ac.[column_id]);
DISCONNECT FROM SQLSVR;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 898 views
  • 3 likes
  • 2 in conversation