Help using Base SAS procedures

New question on previous topic: PROC CONTENTS of entire library with all variables detail

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

New question on previous topic: PROC CONTENTS of entire library with all variables detail

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

 

 


Accepted Solutions
Solution
3 weeks ago
Super User
Posts: 23,776

Re: New question on previous topic: PROC CONTENTS of entire library with all variables detail

Posted in reply to AndreaMacLeod

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

 

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


All Replies
Solution
3 weeks ago
Super User
Posts: 23,776

Re: New question on previous topic: PROC CONTENTS of entire library with all variables detail

Posted in reply to AndreaMacLeod

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

 

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. 

New Contributor
Posts: 2

Re: New question on previous topic: PROC CONTENTS of entire library with all variables detail

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;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

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