topic Re: Why Proc contents does not show the number of observations in my dataset? in SAS Procedures

Why Proc contents does not show the number of observations in my dataset?

Mirisage — Thu, 15 Aug 2013 19:41:44 GMT

Hi SAS Forum;

Could you please help me on this layman question.

I ran the below code for my large data set to see how many records are there.

Proc contents data=huge_data_set varnum;
run;

The output table generated has a dot in front of the "number of observations"
and it does not show the real number of observations in the data set.

Q:Why Proc contents doesn't provide the "number of observations" ?

Thanks for your time
Mirisage

Re: Why Proc contents does not show the number of observations in my dataset?

Astounding — Thu, 15 Aug 2013 20:26:05 GMT

There are several possibilities as to why the number of observations would be unknown.

1. The data is stored on tape.

2. The data is stored on disk, but in tape format.

3. The data set is actually a view rather than a data set.

You probably already know which of these applies ... but if you don't then it's likely to be #2.

Good luck.

Re: Why Proc contents does not show the number of observations in my dataset?

ballardw — Thu, 15 Aug 2013 21:50:01 GMT

And apparently from another thread, if the number records exceeds the maximum value that SAS will store in a numeric variable then Contets reports missing. Depending on how precise you need the record count, you can get an estimate from the the Engine/host dependent information. Use ("Number of data set pages" - "First Data Page")* "Max obs per page".

The first "pages" store the header information and varies depending on numbers of variables and lengths of labels and such.

You could try looking up the number of observations in the Dictionary table Members, but will probably find the number of records there missing as well.

Re: Why Proc contents does not show the number of observations in my dataset?

Mirisage — Sat, 17 Aug 2013 00:20:23 GMT

Hi Astounding and ballardw,

Many thanks to both of you for providing me with this knowledge.

Best regards

Mirisage

Re: Why Proc contents does not show the number of observations in my dataset?

Scott_Mitchell — Sat, 17 Aug 2013 06:07:51 GMT

What happens if you use the following?

%LET DSID = %SYSFUNC(OPEN(WORK.NEWFILES,I));

%LET TOTAL = %SYSFUNC(ATTRN(&DSID.,NOBS));

Does the macro variable store the correct number of observations?

Re: Why Proc contents does not show the number of observations in my dataset?

Mirisage — Sat, 17 Aug 2013 21:38:30 GMT

Hi Scott_Mitchell,

I am very curious to run your code and see.

My very large data set is stored in a warehouse.

Lets say I assign the library name to the warehouse as "location".

Then the proc contenets code that has not provided us with the actual number of observations is like below.

Proc contents data=location.huge_data_set varnum;

run;

I can understand only that much.

Question:

Then what should I do to apply your code?

Is that just copy your below code into my sas editor and running?

%LET DSID = %SYSFUNC(OPEN(WORK.NEWFILES,I));

%LET TOTAL = %SYSFUNC(ATTRN(&DSID.,NOBS));

Please let me know.

Thanks

Mirisage

Re: Why Proc contents does not show the number of observations in my dataset?

Fugue — Sat, 17 Aug 2013 21:55:07 GMT

Take Scott's code, paste it into a SAS program and submit it. Add %put statements to output the results to the LOG. Something like this . . .

%LET DSID = %SYSFUNC(OPEN(WORK.NEWFILES,I));

%LET TOTAL = %SYSFUNC(ATTRN(&DSID.,NOBS));

%PUT the number of observations is &TOTAL;

Re: Why Proc contents does not show the number of observations in my dataset?

Scott_Mitchell — Sun, 18 Aug 2013 02:06:50 GMT

You also need to change WORK.NEWFILES to the name of your dataset LOCATION.HUGE_DATA_SET. Probably self evident, but just in case.

%LET DSID = %SYSFUNC(OPEN(WORK.NEWFILES,I));

%LET TOTAL = %SYSFUNC(ATTRN(&DSID.,NOBS));

%PUT the number of observations is &TOTAL;

Re: Why Proc contents does not show the number of observations in my dataset?

Mirisage — Mon, 19 Aug 2013 17:57:28 GMT

Hi Fugue and Scott_Mitchell,

Thanks. Then I ran the follwing SAS code.

%LET DSID = %SYSFUNC(OPEN(LOCATION.HUGE_DATA_SET,I));

%LET TOTAL = %SYSFUNC(ATTRN(&DSID.,NOBS));

%PUT the number of observations is &TOTAL;

It produced the following log note.

%LET DSID = %SYSFUNC(OPEN(pcfwh.U_PCF_PROPENSITY,I));

17 %LET TOTAL = %SYSFUNC(ATTRN(&DSID.,NOBS));

SYMBOLGEN: Macro variable DSID resolves to 2

19 %PUT the number of observations is &TOTAL;

SYMBOLGEN: Macro variable TOTAL resolves to -1

the number of observations is -1

It seems that the number of observations is not given correctly.

Thanks anyway to both of you.

Mirisage

Re: Why Proc contents does not show the number of observations in my dataset?

ballardw — Mon, 19 Aug 2013 18:06:27 GMT

Which isn't really surprising. The ATTRN function is going to have the same numeric storage/display limitations as any other SAS numeric value.

I would wonder if the data warehouse has an equivalent of the SAS dictionary tables that could be queried.

Re: Why Proc contents does not show the number of observations in my dataset?

Scott_Mitchell — Mon, 19 Aug 2013 23:59:48 GMT

I was hoping for an error or a missing value, as opposed to -1 for number of observations not available.

I can't believe there isn't an accurate way to identify the number of observations in a dataset that size.

Re: Why Proc contents does not show the number of observations in my dataset?

Tom — Tue, 20 Aug 2013 00:23:46 GMT

What do you mean by a 'warehouse'? If it is in some non-SAS database management system (oracle, teradata, etc) then SAS cannot know the number of observations without asking the database.

Re: Why Proc contents does not show the number of observations in my dataset?

Fugue — Tue, 20 Aug 2013 20:58:26 GMT

What version of SAS are you running?

Re: Why Proc contents does not show the number of observations in my dataset?

Fugue — Fri, 23 Aug 2013 00:04:35 GMT

The reason I asked about the version of SAS you are running is that there is an EXTENDOBSCOUNT data set option beginning in SAS 9.3 that is designed for use with large datasets. I haven't thoroughly read the SAS documentation and we don't have datasets anywhere near the necessary size limits, so I'm not sure whether this option will have any effect on observation counts produced by PROC CONTENTS, etc.

Anyone know?

Re: Why Proc contents does not show the number of observations in my dataset?

Mirisage — Fri, 23 Aug 2013 12:24:24 GMT

Hi Tom and Fugue,

Sorry I could not respond you.

For Tom's question, What do you mean by a 'warehouse'? It is a Oracle data base. May be I cannot give the name of the data base for confidentiality

For Fugue's question: I am using SAS Ver 9.2 in Windows. It is emebedded in SAS EG ver 4.3. (My IT knowledge is not good, but I think we can say like ....."embedded in SAS EG").

Thanks every one for this discussion and great knowledge.

Regards

Mirisage

Re: Why Proc contents does not show the number of observations in my dataset?

Scott_Mitchell — Fri, 23 Aug 2013 12:58:40 GMT

Could he use sql pass thru and run a query to count the number of rows and convert the results to character before they are passed back to SAS?

You could also try the system table all_tables column num_rows however this isn't always up to date.

Re: Why Proc contents does not show the number of observations in my dataset?

Tom — Fri, 23 Aug 2013 13:23:49 GMT

If you run PROC CONTENTS on tables/views that are in a foreign database (Oracle, Teradata, etc) then it will not report the number of observations. It has nothing to do with the size of the table. When SAS writes a SAS dataset it stores the number of observations where PROC CONTENTS can retrieve it without counting all of the rows. It cannot do that for external databases.

Re: Why Proc contents does not show the number of observations in my dataset?

Mirisage — Sat, 24 Aug 2013 03:44:32 GMT

Hi Tom,
Thank you for your nice clarification .

Hi Scott_Mitchell,

Thank you for your ontribution.

Regards

Mirisage