HOW TO COUNT NUMBER OF OBSERVATION IN A EXCEL FILE WITHOUT IMPORTING IN SAS ENVIRONMENT

[ANIRBAN2]

%macro obs_count(input=);
%global dsid rc cnt;

 %let rc=%sysfunc(filename(data,&input)); 
 %if &rc.=0

%then %put success; */
%let did = %sysfunc(fopen(&data));

%if &did.>0 %then
%put can open;            */MY EXTERNAL FILE CAN BE OPENED*/

%if &did %then
%do;
%let cnt=%sysfunc(attrn(&did, NVARS));   /*THIS GIVES FOR A DATASE I WANT FOR AN EXTERNAL EXCEL FILE*/
/*%put &cnt.;
%end;*/

/* %let rc=fdelete('testdir'); */
%let rc = %sysfunc(close(&did));

%mend;

%obs_count(input=/home/u60099960/sasuser.v94/sashelp.xlsx)

[andreas_lds]

Please stop writing text in upcase.

Afaik you can't get the number of obs without reading the file.

[japelin]

One way would be to treat the xlsx file as a zip archive and get the value of the dimension ref element in book\xl\worksheets\sheet1.xml.

[andreas_lds]

After exporting sashelp.class using proc export i see

<dimension ref="A1:E1048576"/>

in the xml-file. E1048576 seems to be wrong 😉

[ballardw]

In practicality I would say it can't be done. Even importing the data you can have issue with actual observations versus rows of data in Excel.

Multiple header rows means any count of just "rows" occupied by Excel would be off by the number of header rows minus 1 for most cases.

Also Excel has an annoying habit for manually edited files of creating "phantom" occupied cells. A cell that has ever been populated even if deleted can create rows with no actual data that will be treated by Excel as "observations" with all missing values.

I get lots of Excel files that have as many as 20,000 "rows" of all missing values.

BTW, this one reason that Excel files can get pretty large even though not much read data is in them. These marked cells all have overhead.

[ANIRBAN2]

What is the purpose of fopen function ?What operation can be done after opening a external file using fopen ??

[Tom]

Once you have a file opened with FOPEN() you can then begin reading the lines from it using FREAD().  And close it with FCLOSE().   It has no value for this problem and very little value for most problems.   You could read an XSLX file as binary bytes using FREAD() but you could not make any sense of it.  And if the file was really just a text file then just run a data step to read it.  No need to use these file functions.

If you want to see it put to use check out this macro:

https://github.com/sasutils/macros/blob/master/fread.sas