03-07-2018 04:33 PM - edited 03-07-2018 04:34 PM
I'm trying to count the number of observations in a dataset, accounting for WHERE clauses and the FIRSTOBS and OBS options. So, I open the dataset and read the NLOBSF attribute with the attrn function. However, the count that I get does not account for FIRSTOBS or OBS. Here's an example program, using the sashelp.class dataset, which has 19 total observations:
* macro to count the number of observations in a dataset using the NLOBSF attribute; %macro check(dsn); %let dsid = %sysfunc(open(&dsn)); %if &dsid = 0 %then %do; %put %sysfunc(sysmsg()); %return; %end; %put nlobsf = %sysfunc(attrn(&dsid,NLOBSF)); %let rc = %sysfunc(close(&dsid)); %mend; options firstobs=1 obs=max; * set firstobs and obs to system defaults; %check(sashelp.class); * count all observations -> gives 19 obs (correct); %check(sashelp.class(where=(name=:'J'))); * names starting with J -> gives 7 obs (correct); %check(sashelp.class(firstobs=10)); * skip the first 9 obs -> gives 19 obs (wrong!); %check(sashelp.class(obs=10)); * only the first 10 obs -> gives 19 obs (wrong!); options obs=10; %check(sashelp.class); * only the first 10 obs -> gives 19 obs (wrong!); * with the obs=10 option in effect, this data step correctly reads in only 10 obs; data _null_; set sashelp.class; run;
I get the correct total count when not subsetting. I also get the correct count when using a WHERE clause. But the code fails when using FIRSTOBS or OBS, either as a dataset-option or as a system option. Is this a bug in SAS, or am I doing something wrong / misunderstanding something?
FYI, I'm using SAS 9.4 (TS1M3). Here's the SAS 9.4 help file description of the NLOBSF attribute:
specifies the number of logical observations (the observations that are not marked for deletion) by forcing each observation to be read and by taking the FIRSTOBS system option, the OBS system option, and the WHERE clauses into account.
03-07-2018 04:41 PM
Interesting, but what question are you trying to answer by querying the NOBS, NLOBS or NLOBSF metadata?
It sounds like it is returning the count of observations in the database and you want to know the number of observations that would be read if you tried to query in a way where all of these other external options could potentially change how many observations are actually used.
If you want the latter then you probably will get the best results using PROC SQL to run COUNT(*) on the dataset.
03-07-2018 05:18 PM - edited 03-07-2018 05:21 PM
My goal is to create a reusable macro function that returns the number of observations in a dataset, accounting for any subsetting options. Although I could use proc sql as you suggest, I was hoping to use only macro code, so that I can call my function inline, e.g.
title "Characteristics of male students, N = %nobs(sashelp.class(where=(sex='M')))";
where %nobs is the function I'm creating. This example would actually work, because WHERE clauses are handled correctly using NLOBSF. But if for some reason I wanted to subset with FIRSTOBS or OBS, the code wouldn't work as is, even though the documentation suggests that it should.
03-07-2018 06:14 PM - edited 03-07-2018 06:14 PM
It does appear to NOT honor either system or dataset options for OBS or FIRSTOBS.
If you care about that then perhaps you can make your macro check the system settings using GETOPTION() function and scan the dataset parameter value for OBS= or FIRSTOBS= option. You could then have the macro use FETCH() to read the observations one by one and count.
You can see an example of how to do the counting in this macro
03-07-2018 09:07 PM
The documentation is pretty clear: the FIRSTOBS system option, the OBS system option, and the WHERE clause should be taken into account.
If I were you, I'd definitely ask tech support to open a defect.
Having said that, your macro is mostly useful for discovering data with a where clause applied.
The fact that options FIRSTOBS and OBS are ignored does not diminish its usefulness much, imho.