topic Re: Get ALL SAS library name with Data set names, columns,no of rows,distinct rows,no of columns in SAS Programming

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

sivastat08 — Fri, 01 Mar 2019 12:16:14 GMT

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

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

PaigeMiller — Fri, 01 Mar 2019 13:40:09 GMT

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;

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

RW9 — Fri, 01 Mar 2019 13:46:01 GMT

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.

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

Peter_C — Fri, 01 Mar 2019 14:33:53 GMT

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)

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

sivastat08 — Fri, 01 Mar 2019 17:08:02 GMT

Thanks for the answer Paige Miller.

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

sivastat08 — Tue, 05 Mar 2019 17:00:14 GMT

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

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

Peter_C — Tue, 05 Mar 2019 17:46:26 GMT

Need to generate a blank before OUT=

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

sivastat08 — Tue, 05 Mar 2019 17:57:18 GMT

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.

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

Peter_C — Tue, 05 Mar 2019 22:55:15 GMT

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

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

Peter_C — Tue, 05 Mar 2019 23:05:44 GMT

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)

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

RW9 — Wed, 06 Mar 2019 08:48:12 GMT

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().

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

sivastat08 — Wed, 06 Mar 2019 11:08:55 GMT

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