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;
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;
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.
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.
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
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.
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;
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.
@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.
@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.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.