DATA Step, Macro, Functions and more

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

Reply
Regular Contributor
Posts: 186

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

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

Contributor
Posts: 32

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

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

Regular Contributor
Posts: 186

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

Posted in reply to sandyming

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.

Super Contributor
Posts: 334

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

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?

EJ

Super Contributor
Posts: 334

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

Does a sql count produce results?

proc sql;

     select count(*) as cnt

     from hugetable

     ;

quit;

Regular Contributor
Posts: 186

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

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.

SAS Employee
Posts: 26

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

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.

Contributor
Posts: 38

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

Posted in reply to jwsquillace

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

Super User
Posts: 11,343

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

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.

Regular Contributor
Posts: 186

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

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

But I tried it on smaller tables and it works.

Ask a Question
Discussion stats
  • 9 replies
  • 457 views
  • 1 like
  • 6 in conversation