topic Re: Count no. of obs from multiple datasets in SAS Programming

Count no. of obs from multiple datasets

David_Billa — Mon, 25 Nov 2019 07:35:08 GMT

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;

Re: Count no. of obs from multiple datasets

Lucy1 — Mon, 25 Nov 2019 07:46:07 GMT

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;

Re: Count no. of obs from multiple datasets

andreas_lds — Mon, 25 Nov 2019 08:41:31 GMT

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.

Re: Count no. of obs from multiple datasets

s_lassen — Mon, 25 Nov 2019 08:42:07 GMT

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.

Re: Count no. of obs from multiple datasets

David_Billa — Mon, 25 Nov 2019 08:46:52 GMT

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.

Re: Count no. of obs from multiple datasets

David_Billa — Mon, 25 Nov 2019 09:04:18 GMT

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;

Re: Count no. of obs from multiple datasets

Amir — Mon, 25 Nov 2019 09:13:26 GMT

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.

Re: Count no. of obs from multiple datasets

Lucy1 — Mon, 25 Nov 2019 09:18:51 GMT

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

Re: Count no. of obs from multiple datasets

s_lassen — Mon, 25 Nov 2019 11:19:12 GMT

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.

Re: Count no. of obs from multiple datasets

PaigeMiller — Mon, 25 Nov 2019 13:15:03 GMT

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

Re: Count no. of obs from multiple datasets

ballardw — Mon, 25 Nov 2019 16:02:32 GMT

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