topic Displaying total number of OBS in each Datasets in ODS and Base Reporting

Displaying total number of OBS in each Datasets

Yennie — Wed, 28 Oct 2009 04:29:34 GMT

Hi All...

Just want to say thank you so much to Cynthia for your help!

I am almost close to finishing my project.

I have 15 Datasets at the moment and just wondering is there any Proc steps that I can use to display the total number of obs in each of thesedatasets.

For example... Something like this...

Name of Dataset Number of Obs
DatasetA 1002541
DatasetB 599
DatasetC 1400000
DatasetD 256398

Thanks in advance!!!
Cheers.

Yennie

Re: Displaying total number of OBS in each Datasets

Cynthia_sas — Wed, 28 Oct 2009 06:48:23 GMT

Hi:
Take a look at the "DICTIONARY" tables that you can query with PROC SQL or the SASHELP. Views that you can use with PROC PRINT or PROC REPORT.

Documentation about using these tables to report on your dataset specific information can be found here:
http://support.sas.com/documentation/cdl/en/sqlproc/62086/HTML/default/a001385596.htm

This documentation link contains an example that shows how to display a list of datasets and the number of observations.

cynthia

Re: Displaying total number of OBS in each Datasets

Yennie — Thu, 29 Oct 2009 00:03:02 GMT

Hi Cynthia,

Thank you so much for your prompt reply. I tried the example which you gave me and this is my coding (which I did not change much at all)

Proc SQL;
Title "All Datasets and Views in Asset Risk Library";
Select Libname, Memname, Memtype, Nobs
From Dictionary.Tables
Where Libname = 'SQL';

However, Log gave me an error which says "NOTE: No rows were selected."

Just wondering if you can help me out with this one here? Am I suppose to embed Dictionary.Table in my Library?

Thanks In Advance!

Yennie

Re: Displaying total number of OBS in each Datasets

Cynthia_sas — Thu, 29 Oct 2009 04:49:52 GMT

Hi:
It's very likely that you don't have a permanent library assigned that is named 'SQL'. You have to at least know the name of a LIBRARY that you want to find out the information about. For example, you probably do have a SASHELP Library assigned.

You could try this:
[pre]
Proc SQL;
Title "All Datasets and Views in the SASHELP Library";
Select Libname, Memname, Memtype, Nobs
From Dictionary.Tables
Where Libname = 'SASHELP';
quit;

[/pre]

Next, you will need to figure out where your datasets of interest reside. Let's say that you have a bunch of SAS datasets stored in this physical location:
c:\datafiles\accounting\

so you make a LIBNAME statement and change the code:
[pre]
LIBNAME MYDATA 'C:\datafiles\accounting';

Proc SQL;
Title "All Datasets and Views in the MYDATA Library";
Select Libname, Memname, Memtype, Nobs
From Dictionary.Tables
Where Libname = 'MYDATA';
quit;
[/pre]

Hope that helps.

cynthia

Re: Displaying total number of OBS in each Datasets

Yennie — Thu, 29 Oct 2009 05:02:04 GMT

Hey Cynthia!

I made a mistake. It was a typo! My permanent library is known as "Arisk" therefore now the script works!!!

Last Question: Just wondering, there are a total of 100 datasets in my libray however I am just wanting the query to display that 15 datasets that I want. do you know how to get around it?

thank you so much for you help genius!!

Cheers,
Yennie

Re: Displaying total number of OBS in each Datasets

Cynthia_sas — Thu, 29 Oct 2009 06:29:27 GMT

Hi:
Look at changing your WHERE clause to get only the list of datasets that you want -- using the MEMNAME variable:
[pre]
Proc SQL;
Title "All Datasets and Views in the SASHELP Library";
Select Libname, Memname, Memtype, Nobs
From Dictionary.Tables
Where Libname = 'SASHELP' and
memname in ('CLASS', 'SHOES', 'PRDSALE');
quit;
[/pre]

...this code would list only the 3 datasets that meet the IN condition.

You can put as many dataset names as you want inside the parentheses for IN, do note that the values are expected to be in UPPER case and quoted. PROC SQL uses standard WHERE clause operators, so if you can identify conditions with different logic, then go for it.

cynthia