topic Re: Number of observations for each view in a View Library in SAS Programming

Number of observations for each view in a View Library

procsql — Tue, 12 Feb 2019 01:02:53 GMT

Hello wonderful SAS community!

I have a SAS View library with about 500 views. I need a way to produce a dataset with all of the view names along with its respective number of observations for each view. I tried various datasets/views in the SASHELP library; however, none provided the number of observations for the data step views. Any guidance from the community would be much appreciated.

For context, I am using SAS EG 7.1.

Thank you!

Re: Number of observations for each view in a View Library

Reeza — Tue, 12 Feb 2019 01:13:33 GMT

Views are dynamic and only created when called. Depending exactly how the query is built, it’s possible to change on different runs, for example if you have a date time filter to pull everything as of end of day yesterday. That would change if you ran it tomorrow.

Otherwise, you can use the solution outlined here a few hours ago.

https://communities.sas.com/t5/New-SAS-User/Row-by-Row-operation-in-a-dataset/m-p/534645

@procsql wrote:

Hello wonderful SAS community!

I have a SAS View library with about 500 views. I need a way to produce a dataset with all of the view names along with its respective number of observations for each view. I tried various datasets/views in the SASHELP library; however, none provided the number of observations for the data step views. Any guidance from the community would be much appreciated.

For context, I am using SAS EG 7.1.

Thank you!

Re: Number of observations for each view in a View Library

Tom — Tue, 12 Feb 2019 01:22:51 GMT

By definition if you want to know how many observations a VIEW will return if someone actually queried it you need to query the view.

Here is an example for how to generate the queries to get the counts from the list of views.

If you have more than a hand full of views then you will need to modify the method to not use a macro variable to store the code. You could use a macro or a data step to generate the queries instead.

data ds1;
 set sashelp.class;
run;
data view1/view=view1;
 set ds1;
run;

proc sql noprint ;
create table sizes as
select 
  a.libname
 ,a.memname
 ,a.memtype
 ,b.nobs
from dictionary.members a
inner join dictionary.tables b
on a.libname=b.libname and a.memname=b.memname
where a.libname='WORK'
  and a.memname in ('DS1','VIEW1') 
;
select catx(' ','update sizes set nobs = (select count(1) from',catx('.',libname,memname),')')
  into :code separated by ';'
  from sizes
  where memtype='VIEW'
;
%put %superq(code);
&code ;
quit;

Obs    libname    memname    memtype    nobs

 1      WORK       DS1        DATA       19
 2      WORK       VIEW1      VIEW       19

Re: Number of observations for each view in a View Library

procsql — Tue, 12 Feb 2019 01:39:57 GMT

Thank you for this and the ultra quick reply. I'll give this a try.

Re: Number of observations for each view in a View Library

procsql — Tue, 12 Feb 2019 01:40:43 GMT

Thank you for this and the ultra quick reply! I will definitely give this a try.