Count no. of obs from multiple datasets

[David_Billa]

I'm trying to write the code to count the number of observations from multiple datasets as below.

I'm creating the datasets as below and I want to sum the number of observations from all the datasets which was created in the previous step and I want that value to display in the macro variable 'obsunmber'.

I would like to know whether the proc sql step (second step) which I wrote below will accompoish the task which I wanted to. I'm unable to test the below code at the moment as well due to server outage.

/******create all the rows from the foreign key table if there are rows which has not match the primary key************/
proc sql noprint;
	create table fkeys&i. as select a.* from &_INPUT. as a left outer join &libname..&dsnname. as b 
		on a.&fieldname.=b.&fieldname1.
	where b.&fieldname1. IS NULL;
quit;

/********* Retrieve the number of observations in the dataset ***************/

proc sql noprint;
	select count(*) into: obsnumber from fkeys&i.;
quit;

[Lucy1]

The proc Sql step you have written will do as you require. You could also achieve with a data step with out reading the data (using compile time option nobs)

Data _null_;

  Call symputx('onsnumber',numobs);

  Stop;

  Set fkeys&I nobs=numobs;

Run;

[andreas_lds]

Querying the view sashelp.vtable might be faster than counting the observations.

proc sql noprint;
  select nobs
    into :odsnumber trimmed
    from sashelp.vtable
      where libname = 'WORK' and memname = "FKEYS&i."
  ;
quit;

Also note, that if executed in a loop, the varaible odsnumber will contain the number of observations of the last iteration only.

[David_Billa]

But I want to see the number of observations from all the iterations not only the last one. It would be good if I see the sum of observations from all the iterations.

[Lucy1]

Macro variable references, like &i, will not automatically reference all values of i - only the latest value you gave it (i.e. 1 OR 2 OR 3, not 1 then 2 then 3) so although your first code was correct you will need to use the macro language to loop round all values of i and keep a tally. Amir has provided a good solution for the tallying aspect of your question

[s_lassen]

I want to see the number of observations from all the iterations not only the last one

Then you should add &SQLOBS to your running total inside your macro, e.g.:

Initialize the total before calling the macro:

%global obsnumber;
%let obsnumber=0;

And inside the macro, after the SQL statement:

%let obsnumber=%eval(&obsnumber+&sqlobs);

Just make sure that you do not have a %LOCAL macro variable of the same name, then that will be updated instead.

[David_Billa]

I ran the code below and I got 'odsnumber' value as 19 and what I except is 20.

%macro test;

%let i=1;

data test&i.;
set sashelp.class(obs=1);
run;

%let i=2;

data test&i.;
set sashelp.class;
run;

proc sql noprint;
  select nobs
    into :odsnumber trimmed
    from sashelp.vtable
      where libname = 'WORK' and memname = "TEST&i."
  ;
quit;

%put &odsnumber.;

%mend;
%test;

[Amir]

As in your question, if you want to get the total number of observations then you can achieve this by keeping a running total.

For example, the following code creates data sets and then keeps a total of the number of observations created, which can be seen in the log:

%let total_nobs = 0;

%macro total_nobs;
   %do i = 1 %to 3;

      data class&i;
         set sashelp.class;
      run;

      data _null_;
         call symputx('total_nobs', sum(&total_nobs,nobs));
         set class&i(obs = 0) nobs = nobs;
      run;

   %end;
%mend total_nobs;

options mprint;
%total_nobs;

%put total_nobs = &total_nobs;

Kind regards,

Amir.

[PaigeMiller]

---

@David_Billa wrote:

I ran the code below and I got 'odsnumber' value as 19 and what I except is 20.

%macro test;

%let i=1;

data test&i.;
set sashelp.class(obs=1);
run;

%let i=2;

data test&i.;
set sashelp.class;
run;

proc sql noprint;
  select nobs
    into :odsnumber trimmed
    from sashelp.vtable
      where libname = 'WORK' and memname = "TEST&i."
  ;
quit;

%put &odsnumber.;

%mend;
%test;

---

I really suggest you stay away from using DATA steps to do this type of counting, as a data step has to read every single observation to count them all. This will be very time consuming. The approach from @andreas_lds where you query the SASHELP.VTABLE, where the number of observations is stored, is the way to go.

[ballardw]

---

@David_Billa wrote:

I ran the code below and I got 'odsnumber' value as 19 and what I except is 20.

%macro test;

%let i=1;

data test&i.;
set sashelp.class(obs=1);
run;

%let i=2;

data test&i.;
set sashelp.class;
run;

proc sql noprint;
  select nobs
    into :odsnumber trimmed
    from sashelp.vtable
      where libname = 'WORK' and memname = "TEST&i."
  ;
quit;

%put &odsnumber.;

%mend;
%test;

---

You ONLY "counted" from "Test&i" when &I=2. So you only get the 19 from the second created data set.

You might have been thinking about something similar to and memname like "%TEST%" or maybe substr(memname,1,4)="TEST" but the equals comparison only is going to look at a single value. That single value would have to be something that is not the data set complete name to find two data set values.

[s_lassen]

SQL automatically creates a macro variable, SQLOBS, which contains the number of observations from the last statement. So you can just use 

%let obsnumber=&sqlobs;

right after your query.