topic Re: add the number of obs in each dataset in SAS Programming

add the number of obs in each dataset

jojo — Thu, 20 Dec 2012 17:54:55 GMT

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.

Re: add the number of obs in each dataset

ballardw — Thu, 20 Dec 2012 18:09:21 GMT

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*/

);

Re: add the number of obs in each dataset

jojo — Thu, 20 Dec 2012 18:17:02 GMT

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.

Re: add the number of obs in each dataset

Haikuo — Thu, 20 Dec 2012 19:26:00 GMT

%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

Re: add the number of obs in each dataset

jojo — Thu, 20 Dec 2012 19:43:11 GMT

got a message:

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.

Re: add the number of obs in each dataset

Astounding — Thu, 20 Dec 2012 20:34:00 GMT

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

Re: add the number of obs in each dataset

Tom — Thu, 20 Dec 2012 20:47:43 GMT

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

Re: add the number of obs in each dataset

jojo — Fri, 21 Dec 2012 16:43:45 GMT

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

Re: add the number of obs in each dataset

Astounding — Fri, 21 Dec 2012 17:25:58 GMT

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

Re: add the number of obs in each dataset

Haikuo — Fri, 21 Dec 2012 17:35:51 GMT

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