topic Count number of observation macro won't work on huge table in SAS Programming

Count number of observation macro won't work on huge table

nicnad — Tue, 02 Jul 2013 17:25:24 GMT

Hi,

I found the following macro here : SAS(R) 9.2 Macro Language: Reference (Example 5)

%macro obsnvars(ds);

%global dset nvars nobs;

%let dset=&ds;

%let dsid = %sysfunc(open(&dset));

%if &dsid %then

%do;

%let nobs =%sysfunc(attrn(&dsid,NOBS));

%let nvars=%sysfunc(attrn(&dsid,NVARS));

%let rc = %sysfunc(close(&dsid));

%put &dset has &nvars variable(s) and &nobs observation(s).;

%end;

%else

%put Open for data set &dset failed - %sysfunc(sysmsg());

%mend obsnvars;

%obsnvars(mylib.my_huge_table)

The macro works great on small datasets but for some reason I get this result :

mylib.my_huge_table has 27 variable(s) and -1 observation(s)

Anyone knows how to solve this?

Thank you for your help and time

Re: Count number of observation macro won't work on huge table

sandyming — Tue, 02 Jul 2013 17:45:35 GMT

I doubt it has anything to do with the size of table. Double check the macro and mylib.my_huge_table.

Re: Count number of observation macro won't work on huge table

nicnad — Tue, 02 Jul 2013 17:54:33 GMT

When opening mylib.my_huge_table I see that there is a lot of records. Also I tried the macro on other tables and it works fine.

Re: Count number of observation macro won't work on huge table

esjackso — Tue, 02 Jul 2013 17:57:21 GMT

I found this that may provide some insight:

36112 - Managing large SAS® data sets that exceed the maximum number of observations

Are we talking about tables as large as those in the article?

Re: Count number of observation macro won't work on huge table

esjackso — Tue, 02 Jul 2013 18:01:08 GMT

Does a sql count produce results?

proc sql;

select count(*) as cnt

from hugetable

;

quit;

Re: Count number of observation macro won't work on huge table

nicnad — Tue, 02 Jul 2013 18:35:13 GMT

Hi EJ

Thank you for the reply. I tried running a simple sql count and the query is still running ( I started it 5 hours ago...)

When I try this code :

data _null_;

put nobs=;

stop;

set mylib.my_huge_table nobs=nobs;

run;

I get this result : nobs=9.0071993E15 that would mean, if I understand the result correctly, that there is 9.0071993 * 10^15 records, which means more than nine hundred million billions.... I'm not sure if that result is accurate. This is why I wanted to run the macro on this table.

Could you please provide me with more guidance on this issue?

Thank you for your help and time.

Re: Count number of observation macro won't work on huge table

jwsquillace — Tue, 02 Jul 2013 19:07:59 GMT

From your description, it sounds like the observation counter has overflowed and cannot accurately contain the number of observations in the SAS data set. This limits access to sequential, prevents indexing and generally turns your data set into a giant tape file.

Here is an option:

*IF* you are running SAS 9.3 or later, you can recreate your SAS data set using the data set option like this:

data newdataset(EXTENDOBSCOUNT=YES);

set olddataset; run;

I highly recommend using a different data set name, not reusing the original data set name.

The new copy of the data set will have a different internal structure than the original, but will allow you to query the number of observations, create indexes and access the data set randomly using POINT= option.

The down side is that the new data set cannot be read by SAS 9.2 or earlier releases due to the internal structure change.

Re: Count number of observation macro won't work on huge table

ehsanmath — Wed, 10 Jul 2013 15:21:44 GMT

Here i have one more question about the last suggestion in the above post.

If you get the following error in SAS Log,

"ERROR: File MYFILES.BIGFILE contains 2G -1 observations and cannot hold more because it contains an index or an Integrity Constraint that uses an index"

then you need to set EXTENDOBSCOUNTER=YES to get rid of above error. Such an error happens when you are working with very big data sets(36112 - Managing large SAS® data sets that exceed the maximum number of observations). Actullay, sas documentation says, by specifying the EXTENDOBSCOUNTER= option when you create an output SAS data file, the resulting 32-bit file behaves like a 64-bit file regarding counters.

I have observed after setting the option "EXTENDOBSCOUNTER=YES" the performance of sas decreses i.e. SAS takes more time for the same task.

My question is: does anybody know about this option and does it really effect the performance of SAS?

regards

ehsan

Re: Count number of observation macro won't work on huge table

ballardw — Wed, 10 Jul 2013 15:33:53 GMT

Does this work:

proc sql;

title "Number of observations in Mylib.my_huge_table";

select nobs

from dictionary.tables

where libname="MYLIB" and MEMNAME="MY_HUGE_TABLE";

QUIT;

The values of the library name and member name must be in capital letters as that is they way they are stored in the dictionary tables.

Re: Count number of observation macro won't work on huge table

nicnad — Wed, 10 Jul 2013 17:11:49 GMT

For this specific table, it is not working. The result is blank.

But I tried it on smaller tables and it works.