How to find is specific dataset available ( with observations) in the active libraries.

[SASuserlot]

Hi,

I  have some requirement, where I need to check is specific dataset exist in the active libraries with observations or not! and finally create the dataset that  gives overview of the libraries. Is it possible? I will try to give the example below.

Example. I have a default macro that creates the active libraries like Feb, March, April which contains Expense, and or debt datasets in the respective libraries (  since I cannot give the macro, I wrote individual libnames in the code). And finally create dataset  given in the image. I do have the idea about %sysfunc (exist (xx)) but not sure How to use to achieve this.

 libname feb 'c\desktop\New folder3';

 data feb.debt;
 month= feb ;
 debt=500;
 run;

 libname march 'c\desktop\New folder2';

 data March.expense;
 run;

 libname april 'c\desktop\New folder1';

 data april.expense;
 month=april;
 expense=200;
 run;

My requirement is

1. Check  each library for 'expense' dataset available or not?

2. if the 'Expense' dataset available then check for is there any observations available or not.(Note: March library have the dataset but no observations in it.)

Ps: We have the flexibility to modify the datasets creation (I given best of my abilities to simulate my requirement).

Thank you.

[Reeza · Accepted solution]

The sashelp.vtable data set contains a list of all assigned libraries and their data sets and the number of observations. 

You can filter that data set for the libraries and data sets of interest.

Are your library names actually month names? Would it check up to current month (August), previous month (July) or end of the year?

proc sql;
create table want as
select libname, memname, nobs, from sashelp.vtable
where libname in (list of libraries)
and memname in ('EXPENSE', 'DEBT');
quit;

Then wrangle it to your desired format.

[SASuserlot]

Thank you @Reeza . My libraries are not month names I just created for Example purpose. Your solution satisfies my requirement except, In my 'March' Library the 'expense' dataset don't have any observations  and April have one Observation. But my 'Want' dataset shows there is ' one' observation in  March. Expense dataset. What I am doing wrong here?

proc sql;
create table want as
select libname, memname, nobs from sashelp.vtable
where upcase(libname) in ('APRIL' 'MARCH' 'FEB')
and upcase(memname) in ('EXPENSE', 'DEBT');
quit;

[Reeza]

Once a data set exists in SAS it has at least one row recorded. If you need to check if that row has actual information you'll need to scan the table. If you have control over this process it's better to fix this issue before this process, where if the table has no data it is not created at all....

[SASuserlot]

Thank you.

[Tom]

---

@Reeza wrote:

Once a data set exists in SAS it has at least one row recorded. If you need to check if that row has actual information you'll need to scan the table. If you have control over this process it's better to fix this issue before this process, where if the table has no data it is not created at all....

---

It is definitely possible to create a dataset with zero observations, and DICTIONARY.TABLES will show that.

Here is an example;

1    data empty;
2      stop;
3    run;

NOTE: The data set WORK.EMPTY has 0 observations and 0 variables.
NOTE: DATA statement used (Total process time):
      real time           0.03 seconds
      cpu time            0.03 seconds


4    proc print data=sashelp.vtable;
NOTE: Writing HTML Body file: sashtml.htm
5      where memname='EMPTY' and libname='WORK';
6      var libname memname nobs;
7    run;

NOTE: There were 1 observations read from the data set SASHELP.VTABLE.
      WHERE (memname='EMPTY') and (libname='WORK');

Result

[Reeza]

It's possible but most code doesn't have a STOP so if it errors out that won't happen 😞

[Tom]

---

@Reeza wrote:

It's possible but most code doesn't have a STOP so if it errors out that won't happen 😞

---

Huh?

8    data empty;
9     set sashelp.class;
10    where age < 5 ;
11   run;

NOTE: There were 0 observations read from the data set SASHELP.CLASS.
      WHERE age<5;
NOTE: The data set WORK.EMPTY has 0 observations and 5 variables.

Steps that fail is a different question.