add the number of obs in each dataset

[jojo]

I need to count obs in each dataset created in a macro and add them up to get the sum . My code:

%macro p (num1, num2);

%do i=2 %to &num1;

  %do j=2 %to &num2;

          %s1(&a,&b,&c,&d); *** create data2 here;

     *** I need to count the number of obs in each data2 created in each loop and get the total number of obs in all data2;                            

%end;%end;%end;%end;

%mend;

Any help will be appreciated.

[ballardw]

Do you need to count, as the individual counts are used, or just the total number of observations? Are these separate datasets or are you overwriting Data2 each time through the loop?

If you have not been overwriting and each output set has it's own name and been clever and placed them in there own library the following might help:

proc sql;

select sum(nobs) AS TotalObs

from dictionary.tables

where libname='SASHELP'  /* your library here*/

;

QUIT;

if you have other sets in that library you don't want to include then an additional part of the where clause woud be needed similar to

where libname='YOURLIBNAME' and memname in (

/* a repeat of your macro looping that results in each dataset name in upper case*/

);

[jojo]

the individual counts are not used, only the total sum is used, but there will be some empty data2, I need to exclude them. I am overwriting the data2.

[Haikuo · Accepted solution]

%macro p (num1, num2);

%GLOBAL TOTAL;

%LET TOTAL=0;

%do i=2 %to &num1;

  %do j=2 %to &num2;

     %s1(&a,&b,&c,&d); *** create data2 here;

  *** I need to count the number of obs in each data2 created in each loop and get the total number of obs in all data2;

proc sql;

select CATS(nobs) into :nobs from dictionary.tables where LIBNAME='WORK' AND UPCASE(MEMNAME)='DATA2';QUIT;

  %LET TOTAL=%EVAL(&NOBS.+&TOTAL.);

%end;%end;%end;%end;

%mend;

%PUT TOTAL= &TOTAL;

Basically you add up total records to a macro variable after each time a new table being built or rewrite.

Haikuo

[jojo]

got a message:

WARNING: Apparent symbolic reference NOBS not resolved.

WARNING: Apparent symbolic reference NOBS not resolved.

ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric operand

is required. The condition was: &NOBS.+0

ERROR: The macro P will stop executing.

[Astounding]

The solution looks right.  Most likely, you omitted the colon after INTO.

[Tom]

Unless you have syntax error that is probably caused by there NOT being a dataset named DATA2.  You should probably fix this issue first.

Note that when use SQL to generate a macro variable it is usually best to preset the default value that you want in case the query does not find any matching records.

%let nobs=0;

select nobs into :nobs

  from dictionary.tables

  where libname='WORK'

      and memname='DATA2'

;

%let total = %eval(&nobs + &total);

[jojo]

Thanks! the dataset name is correct. after adding the code to preset the default value, there is no error message anymore.

[Astounding]

A small trick should let this work even if you forget to set the default value (as long as the %GLOBAL statement is still there):

%let total = %eval(&total +&nobs);

Leave out the space after "+" and it will generate a legitimate numeric even when &TOTAL is null.  (Also note how CATS removed any leading and trailing blanks from &NOBS.)

[Haikuo]

Liked and learned. The problem for me is that there are too many tricks and I keep forgetting them. The other day it even took me long to understand a piece of short code I wrote myself.

Haikuo