BookmarkSubscribeRSS Feed
nicnad
Fluorite | Level 6

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

9 REPLIES 9
sandyming
Calcite | Level 5

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

nicnad
Fluorite | Level 6

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.

esjackso
Quartz | Level 8

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

esjackso
Quartz | Level 8

Does a sql count produce results?

proc sql;

     select count(*) as cnt

     from hugetable

     ;

quit;

nicnad
Fluorite | Level 6

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.

jwsquillace
SAS Employee

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.

ehsanmath
Obsidian | Level 7

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

ballardw
Super User

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.

nicnad
Fluorite | Level 6

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

But I tried it on smaller tables and it works.

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
  • 9 replies
  • 1489 views
  • 1 like
  • 6 in conversation