DATA Step, Macro, Functions and more

add the number of obs in each dataset

Accepted Solution Solved
Reply
Contributor
Posts: 50
Accepted Solution

add the number of obs in each dataset

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.


Accepted Solutions
Solution
‎12-20-2012 02:26 PM
Respected Advisor
Posts: 3,156

Re: add the number of obs in each dataset

%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

View solution in original post


All Replies
Super User
Posts: 11,343

Re: add the number of obs in each dataset

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

);

Contributor
Posts: 50

Re: add the number of obs in each dataset

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.

Solution
‎12-20-2012 02:26 PM
Respected Advisor
Posts: 3,156

Re: add the number of obs in each dataset

%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

Contributor
Posts: 50

Re: add the number of obs in each dataset

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.

Super User
Posts: 5,497

Re: add the number of obs in each dataset

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

Super User
Super User
Posts: 7,039

Re: add the number of obs in each dataset

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

Contributor
Posts: 50

Re: add the number of obs in each dataset

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

Super User
Posts: 5,497

Re: add the number of obs in each dataset

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

Respected Advisor
Posts: 3,156

Re: add the number of obs in each dataset

Posted in reply to Astounding

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 363 views
  • 10 likes
  • 5 in conversation