topic Re: HOW TO COUNT NUMBER OF OBSERVATION IN A EXCEL FILE WITHOUT IMPORTING IN SAS ENVIRONMENT in SAS Programming

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

ANIRBAN2 — Thu, 03 Mar 2022 10:56:11 GMT

%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)

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

andreas_lds — Thu, 03 Mar 2022 11:25:20 GMT

Please stop writing text in upcase.

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

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

japelin — Thu, 03 Mar 2022 12:01:36 GMT

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.

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

andreas_lds — Thu, 03 Mar 2022 12:16:12 GMT

After exporting sashelp.class using proc export i see

<dimension ref="A1:E1048576"/>

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

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

ballardw — Thu, 03 Mar 2022 14:23:36 GMT

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.

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

ANIRBAN2 — Fri, 04 Mar 2022 03:11:01 GMT

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

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

Tom — Fri, 04 Mar 2022 03:42:27 GMT

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