BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Mishka1
Fluorite | Level 6

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!

1 ACCEPTED SOLUTION

Accepted Solutions
data_null__
Jade | Level 19

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

6 REPLIES 6
art297
Opal | Level 21

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

Mishka1
Fluorite | Level 6

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.

Peter_C
Rhodochrosite | Level 12

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 ;

Tom
Super User Tom
Super User

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;

Howles
Quartz | Level 8

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!

data_null__
Jade | Level 19

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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