topic Re: No of rows in a table in SAS Programming

No of rows in a table

HeatherNewton — Thu, 08 Jun 2023 15:04:21 GMT

How can i get the no of rows in table A and output it as an input of a variable in another table?

Re: No of rows in a table

Reeza — Thu, 08 Jun 2023 15:11:06 GMT

More than one way, as usual.

https://www.listendata.com/2017/04/number-of-observations-in-sas-data.html

Probably the simplest to understand - note the table names are upper case.

proc sql noprint;
 select nobs into :totobs separated by ' ' from dictionary.tables
 where libname='SASHELP' and memname='CARS';
quit;
%put total records = &totobs.;

Use the &totobs value in the data step as needed.

data want;
x=&totobs;
run;

Re: No of rows in a table

Kurt_Bremser — Thu, 08 Jun 2023 15:13:32 GMT

Query the DICTIONARY tables:

proc sql noprint;
select nobs into :number
from dictionary.tables
where libname = "WORK" and memname = "A";
quit;

%put number of obs = &number.;

Re: No of rows in a table

mkeintz — Thu, 08 Jun 2023 16:44:53 GMT

This is a good situation to use the "IF 0 THEN SET .... NOBS=...;" statement, as in

data want;
  if 0 then set table_a (drop=_all_) nobs=number_of_drivers;
  set sashelp.cars (keep=make model msrp);
  perdriver_cost=msrp/number_of_drivers;
run;

Three points:

Even though IF 0 is never true (so the "then SET" is never executed), the value for nobs (stored in number_of_drivers) is established prior to actual data processing. So it's there for usage.
Don't forget the "drop=_ALL_" dataset name parameter for table_a - that will eliminate columns of missing values for variables exclusive to table_a.
The variable in the "nobs=" argument is not a permanent variable. If you want it to be permanent, then modify the "if 0" to something like
if 0 then ... nobs=xxx;
followed by
number_of_drivers=xxx;
xxx will not be kept, but number_of_drivers will be.

A nice feature of this approach is that it doesn't require using a separate procedure to get the number of observations, nor the creation of macrovars, or the use of special functions in the data step.

Re: No of rows in a table

Ksharp — Fri, 09 Jun 2023 12:12:28 GMT

/*
I prefer this code.
*/

%let dsid=%sysfunc(open(sashelp.class));
%let nobs=%sysfunc(attrn(&dsid.,nlobs));
%let dsid=%sysfunc(close(&dsid.));

%put sashelp.class has &nobs records.;

Re: No of rows in a table

HeatherNewton — Tue, 13 Jun 2023 06:46:40 GMT

why you dont need dot here after &totobs

data want;

x=&totobs;

run;

Re: No of rows in a table

Kurt_Bremser — Tue, 13 Jun 2023 07:36:24 GMT

If the macro variable name is not part of a longer string which might be a SAS name, the dot is not needed, but it is considered good practice (especially for beginners!) to always terminate a macro reference with a dot.

Dot-terminated strings will also have distinct coloring in the Enhanced Editor.

See Maxim 48.

Re: No of rows in a table

HeatherNewton — Wed, 14 Jun 2023 23:35:02 GMT

I tried but it is empty in table want, what could have gone wrong..

Re: No of rows in a table

HeatherNewton — Thu, 15 Jun 2023 04:34:29 GMT

I tried this it works abd display answer in log
But how to store the answer in a dataset?

Re: No of rows in a table

Reeza — Thu, 15 Jun 2023 15:14:03 GMT

Post your log please.

Re: No of rows in a table

Reeza — Thu, 15 Jun 2023 15:23:11 GMT

%let dsid=%sysfunc(open(sashelp.class));
%let nobs=%sysfunc(attrn(&dsid.,nlobs));
%let dsid=%sysfunc(close(&dsid.));

%put sashelp.class has &nobs records.;

data num_records;
num_obs = &nobs;
run;

In the solution I posted, the table names need to be capitalized.