Get ALL SAS library name with Data set names, columns,no of rows,distinct rows,no of columns

[sivastat08]

Hi ,

By referring this site "Get a list of dataset names in a directory/library" i got an output of sas Library with column name.

and referring Proc Contents i can able to get no of rows and columns but i am  unable to get distinct rows for each data sets . I am looking for distinct rows of each data sets and referring  'FileName' name column dynamically.Attached sample data.

Can help on this.

Thanks,

Siva

[PaigeMiller · Accepted solution]

The table SASHELP.VTABLE always has this information.

So, if you are interested in all SAS data sets in a library named PAIGE

proc print data=sashelp.vtable(where=(libname='PAIGE' and memtype='DATA'));
    id memname;
    var nobs nvar;
run;

[sivastat08]

Thanks for the answer Paige Miller.

[RW9]

You would need to do something like:

data _null_;
  set sashelp.vtable (where=(libname="<yourlib>"));
  call execute(cats('proc sort data=<yourlib>.',memname,' out=dist_',memname,' nodupkey; by _all_; run;'));
run;

Replace <libname> with your library.  The above will run a proc sort on all the datasets, then you can take from sashelp.vtable number of rows, just like the other datasets.

[sivastat08]

Hi RW ,Thanks for Help.

Below code is selecting the data set names , but it's throwing an error.Could you please help on fixing this.Below are Error logs.

/*Code*/

data _null_;
set sashelp.vtable (where=(libname="STSTGTBL"));
call execute(cats('proc sort data=STSTGTBL.',memname,' out=dist_',memname,' nodupkey; by _all_; run;'));
run;

/*Error1*/

24 data _null_;
25 set WORK.QUERY_FOR_AAA (where=(Library_Name="STSTGTBL"));
26 call execute(cats('proc sort data = STSTGTBL. ',Table_Name,'out= dis_',Table_Name,'nodupkey; by _all_; run;'));
27 run;

NOTE: There were 131 observations read from the data set WORK.QUERY_FOR_AAA.
WHERE Library_Name='STSTGTBL';
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

NOTE: CALL EXECUTE generated line.
NOTE: Line generated by the CALL EXECUTE routine.
1 + proc sort data = STSTGTBL.ABSENTEEISM_NGout= dis_ABSENTEEISM_NGnodupkey; by _all_; run;
_
22
76

ERROR 22-322: Syntax error, expecting one of the following: DATA, DUPOUT, IN, OUT, SIZE, SORTSEQ, SORTSIZE, T, TECH, TECHNIQUE,
UNIOUT, UNIQUEOUT.

ERROR 76-322: Syntax error, statement will be ignored.

NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SORT used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

NOTE: Line generated by the CALL EXECUTE routine.
2 The SAS System 09:21 Tuesday, March 5, 2019

2 + proc sort data = STSTGTBL.ACTIVEDLYSNAPTILL30SEP2018_0000out= dis_ACTIVEDLYSNAPTILL30SEP2018_0000nodupkey; by _all_;
run;
_
22
76
ERROR: Invalid data set name STSTGTBL.ACTIVEDLYSNAPTILL30SEP2018_0000out.

ERROR 22-322: Syntax error, expecting one of the following: DATA, DUPOUT, IN, OUT, SIZE, SORTSEQ, SORTSIZE, T, TECH, TECHNIQUE,
UNIOUT, UNIQUEOUT.

ERROR 76-322: Syntax error, statement will be ignored.

NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SORT used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

[RW9]

Sorry, yes, I hadn't noticed as I didn't have anything to test it on.  Its a simple change:

data _null_;
  set sashelp.vtable (where=(libname="STSTGTBL"));
  call execute(cat('proc sort data=STSTGTBL.',memname,' out=dist_',memname,' nodupkey; by _all_; run;'));
run;

Note, instead of using cats() which strips blanks from start and end of strings, I use cat().

[Peter_C]

Is distinct counting to
apply distinct across all columns in a table
Or distinct counts for each column
(The second approach might be useful too)

[Peter_C]

Need to generate a blank before OUT=

[sivastat08]

I changed it before running ' out=dist_'  i tried running instead of out= but i am getting below error 

/*Code:*/

data _null_;
set sashelp.vtable (where=(libname="STSTGTBL"));
call execute(cats('proc sort data=STSTGTBL.',memname,'out=',memname,' nodupkey; by _all_; run;'));
run;

23 GOPTIONS ACCESSIBLE;

24 data _null_;
25 set sashelp.vtable (where=(libname="STSTGTBL"));
26 call execute(cats('proc sort data=STSTGTBL.',memname,'out=',memname,' nodupkey; by _all_; run;'));
27 run;

NOTE: There were 4 observations read from the data set SASHELP.STSTGTBL.
WHERE libname='VALIB';
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.01 seconds

NOTE: CALL EXECUTE generated line.
NOTE: Line generated by the CALL EXECUTE routine.
1 + proc sort data=VALIB.ATTRLOOKUPout=ATTRLOOKUPnodupkey; by _all_; run;
_
22
76

ERROR 22-322: Syntax error, expecting one of the following: DATA, DUPOUT, IN, OUT, SIZE, SORTSEQ, SORTSIZE, T, TECH, TECHNIQUE,
UNIOUT, UNIQUEOUT.

ERROR 76-322: Syntax error, statement will be ignored.

NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SORT used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

NOTE: Line generated by the CALL EXECUTE routine.
2 The SAS System 11:53 Tuesday, March 5, 2019

2 + proc sort data=STSTGTBL.CENTLOOKUPout=CENTLOOKUPnodupkey; by _all_; run;
_
22
76

ERROR 22-322: Syntax error, expecting one of the following: DATA, DUPOUT, IN, OUT, SIZE, SORTSEQ, SORTSIZE, T, TECH, TECHNIQUE,
UNIOUT, UNIQUEOUT.

ERROR 76-322: Syntax error, statement will be ignored.

[Peter_C]

Errors in generated cide are occurring at

data=VALIB.ATTRLOOKUPout=ATTRLOOKUPnodupkey; by _all_; run;
_
ERROR 22-322: Syntax error, expecting one of the following: DATA, DUPOUT, IN, OUT, SIZE, SORTSEQ, SORTSIZE, T, TECH, TECHNIQUE,
UNIOUT, UNIQUEOUT.


later at
2 + proc sort data=STSTGTBL.CENTLOOKUPout=CENTLOOKUPnodupkey; by _all_; run;

Both cases caused by lack of space before out= and nodupkey

[Peter_C]

If you cannot rearrange the syntax to ensure only a semicolon follows memname, one possibility that might work is to place something to act like a hard blank before out= and nodupkey. I think the syntax
'%str( ) out= ' and
'%str( ) nodupkey; by _all_; run;'

might do it, as long as these have single not double quotes
(or the blank in %( ) would resolve to early)

[sivastat08]

Thanks A lot Peter. I have used the above mentioned  syntax  '%str( ) out= ' and '%str( ) nodupkey; by _all_; run;' .Also The Code is running and generating Outputs.But output data sets are limited to 50 only.

Here is my code:

data _null_;
set sashelp.vtable (where=(libname="MAPSSAS"));
call execute(cats('proc sort data=MAPSSAS.',memname,'%str() out=out_',memname,'%str() nodupkey; by _all_; run;'));
run;

by using this code

proc print data=sashelp.vtable(where=(libname='PAIGE' and memtype='DATA'));
    id memname;
    var nobs nvar;
run;

i am getting output of memname ,Nobs and Nvar, Also i need No of distinct rows for each data sets.will you please help me out.

memname	nobs	nvar	Ndistinct
AFGHANI2	29	4
AFGHANIS	2644	6
AFRICA	52824	8
AFRICA2	61	10
ALGERIA	1617	6
ALGERIA2	9	4
ANDORRA	203	6
ANDORRA2	1	4
ANOMALY	12	10
ANTARCT2	5	10
ANTARCTI	18071	8
ARGENTI2	24	9
ARGENTIN	3852	6