DATA Step, Macro, Functions and more

Exclude a view if it will return zero observations

Accepted Solution Solved
Reply
Contributor
Posts: 54
Accepted Solution

Exclude a view if it will return zero observations

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!


Accepted Solutions
Solution
‎11-01-2011 09:50 AM
Respected Advisor
Posts: 3,799

Re: Exclude a view if it will return zero observations

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;

View solution in original post


All Replies
PROC Star
Posts: 7,492

Exclude a view if it will return zero observations

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

Contributor
Posts: 54

Exclude a view if it will return zero observations

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.

Valued Guide
Posts: 2,177

Exclude a view if it will return zero observations

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 ;

Super User
Super User
Posts: 7,076

Re: Exclude a view if it will return zero observations

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;

Regular Contributor
Posts: 184

Exclude a view if it will return zero observations

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!

Solution
‎11-01-2011 09:50 AM
Respected Advisor
Posts: 3,799

Re: Exclude a view if it will return zero observations

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;

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 219 views
  • 3 likes
  • 6 in conversation