topic Re: Exclude a view if it will return zero observations in SAS Programming

Exclude a view if it will return zero observations

Mishka1 — Mon, 31 Oct 2011 17:25:05 GMT

So I've found a million ways to return the number of observations in a data set but a view seems to be understandably harder.

I have a library full of views and I need to merge them but some of the views don't return anything so it gums up my merge.

So far, I found out how to only pull the views that exist in the library. How do I exclude the ones that will return nothing?

Proc SQL noprint;

Select 'temp.' || memname as ViewName

into :ViewNames separated by ' '

From DICTIONARY.VIEWS

Where UPCASE(LIBNAME)='TEMP'

and UPCASE(MEMTYPE)='VIEW'

;

quit;

data Combined;

set &ViewNames;

run;

Thanks as always!

Exclude a view if it will return zero observations

art297 — Mon, 31 Oct 2011 17:29:36 GMT

Here is a paper that shows a couple of methods for determining the number of records in a view: http://www2.sas.com/proceedings/sugi26/p095-26.pdf

Exclude a view if it will return zero observations

Mishka1 — Tue, 01 Nov 2011 12:42:07 GMT

Thanks Art. I've put a solid 8 hours into trying to solve this with your help so far and still no luck. I'm sure the answer is in the macro MTANYOBS in the paper you suggest but I can't figure out how to get it to run each view in my library.

Can I put an array into the macro somehow so it will loop through all of the views in the library? Or am I thinking of this backwards and should I put the macro into a do loop?

I've tried disecting the macro into just the piece I think I'd need which seems to be the

let DSID = %sysfunc(open(&ViewNames, IS));

if %sysfunc(fetch(&DSID)) = 0 %then

let hasobs = 1;

end;

but once again, I can't get a data set created with the memnames and indicator of observations.

If you can spare some more time I'd appreciate it.

Re: Exclude a view if it will return zero observations

Tom — Tue, 01 Nov 2011 13:42:10 GMT

One way is to try to pull a record from the view. This is done easily with PROC SQL.

Here is a macro that will make a list of all of the good views in a library and put them into a macro variable.

%macro goodviews(libref=work,mvar=goodviews);

%if not %symexist(&mvar) %then %do;

%global &mvar;

%end;

%let &mvar=;

%local i nviews view views flag;

proc sql noprint ;

select catx('.',libname,memname)

into :views separated by ' '

from dictionary.members

where libname="%upcase(&libref)"

and memtype='VIEW'

;

%let nviews=&sqlobs;

%do i=1 %to &nviews;

%let view=%scan(&views,&i,%str( ));

select 1 into :flag from &view;

%if &sqlobs %then %let &mvar=&&&mvar &view;

%end;

quit;

%mend;

%goodviews;

Exclude a view if it will return zero observations

Howles — Tue, 01 Nov 2011 13:46:19 GMT

What is "gummed up"?

I ran

data sumpim / view=sumpim ;

key = 'a' ; satellite_1 = 11 ; output ;

key = 'b' ; satellite_1 = 12 ; output ;

run ;

data nuttin / view=nuttin ;

key = 'c' ; satellite_2 = 21 ; stop ;

run ;

data merged ;

merge sumpim nuttin ;

by key ;

run ;

and found that it prodeced a sensible result.

It is true that there can be satellite variables which are always missing (like SATELLITE_2 in this example), but it may be easier to allow those to accrue and clean them up after the merges are performed.

Mishka1 wrote:
So I've found a million ways to return the number of observations in a data set but a view seems to be understandably harder.
  
I have a library full of views and I need to merge them but some of the views don't return anything so it gums up my merge.
  
So far, I found out how to only pull the views that exist in the library. How do I exclude the ones that will return nothing?
  
Proc SQL noprint; 
      Select 'temp.' || memname as ViewName 
      into :ViewNames separated by ' '
      From DICTIONARY.VIEWS
      Where UPCASE(LIBNAME)='TEMP'
      and UPCASE(MEMTYPE)='VIEW'
;
quit;
  
data Combined;
      set &ViewNames;         
run;
Thanks as always!

Re: Exclude a view if it will return zero observations

data_null__ — Tue, 01 Nov 2011 13:50:11 GMT

You are asking the wrong question. You don't need to know how many obs. You just need to know if there are none. Just check for EOF. Modify this code to process all the views and you are good. You will want some from of CODE GEN.

data class / view=class;
   set sashelp.class;
   where age gt 17;
   run;
data shoes / view=shoes;
   set sashelp.shoes;
   where 0;
   run;
data _null_;
   if eof1 then put 'NO OBS in data set 1';
   if eof2 then put 'NO OBS in data set 2';
   stop;
   set class(drop=_all_) end=eof1;
   set shoes(drop=_all_) end=eof2;
   run;

Exclude a view if it will return zero observations

Peter_C — Fri, 04 Nov 2011 13:25:10 GMT

a big pointer to your answer was posted by data_null;.

Your questions included

Can I put an array into the macro somehow so it will loop through all of the views in the library?

that can be solved with a loop over those views, fetching first row from each.

You should be aware that even just the first row might need a lot of procesing to create.

Beware that a data step view will fill at least the first "page" of data which will be more than one row.

It was handy to use the loop also to describe these views.

First some demo views .

data vEmpty /view= vEmpty ;

stop ;

set sashelp.class ;

run;

data vSome /view= vSome ;

set sashelp.class( obs= 2 ) ;

run;

proc sql ;

create view Vsql1 as select * from sashelp.class( obs= 2 );

create view Vsql0 as select * from sashelp.class( obs= 0 );

quit ;

data view_has_obs ;

set sashelp.vview( where=( libname ne 'SASHELP' )) ;

if engine = 'SASDSV' then

call execute( cats( 'data view= ', libname, '.', memname, '; describe ; run ;' ));

else call execute( 'proc sql; describe view '!! cats( libname, '.', memname,'; quit;' ));

Vid = open( cats( libname, '.', memname), 'is' ) ;

if not VID then do ;

putlog 'VIEW_TROUBLE: ' (_all_)(=) ;

_error_=0 ;

has_obs = . ;

end ;

else do ;

has_obs = ( not fetch( Vid ) ) ;

rc = close( Vid ) ;

end ;

keep libname memname engine has_obs ;

run ;

proc print label data= view_has_obs ;

id libname memname ;

var has_obs engine ;

title j=l 'peterC view info' j=c 'HAS-(any)-OBS' ;

footnote j=r "run %sysfunc(datetime(),twmdy) by &sysuserID" ;

option nodate nonumber;

run ;