topic Re: Macro that counts the number of rows in a data table. Understanding how SAS works in SAS Programming

Macro that counts the number of rows in a data table. Understanding how SAS works

carles — Mon, 15 Apr 2019 15:38:43 GMT

Hello everyone,

I haven't been able to find predefined functions that will work directly on SAS as ncol() or nrow() in R for a dataset.

Therefore, I have tried to replicate the function nrow() using the following logic, trying to store the result in a variable.

My attempt has been as following:

%let CARS= sashelp.cars ; * Storing the predefined dataset in a variable

%macro nrow(TableName);
proc sql;
	select count(*)
	from &TableName;
	quit; 
%mend;

%let res =%nrow(&CARS); * Storing the result of the macro in the variable;
%put &res; * I assume this is like printing (I have read that it passes the number to char also);

However, that does not work.

(1) Can anyone help me out with it respecting the idea of how I have written it (if possible)?

I would appreciate a clear explanation on my mistake rather than a sole correction.

Thank you for your time in advanced.

Re: Macro that counts the number of rows in a data table. Understanding how SAS works

PaigeMiller — Mon, 15 Apr 2019 15:50:28 GMT

%macro nrows(tablename);
%global nrows;
proc sql;
select nobs into :nrows from dictionary.tables  where libname="WORK" and memname="%upcase(&tablename)";
quit;
%mend;

ods listing;
%nrows(abc)

%put &=nrows;

You can make obvious modifications for data sets that are not in the WORK library.

Re: Macro that counts the number of rows in a data table. Understanding how SAS works

tomrvincent — Mon, 15 Apr 2019 15:49:48 GMT

Keeping it simple:

proc sql noprint;
select count(*) into :recs
from sashelp.cars;
quit;

%put &=recs;

Re: Macro that counts the number of rows in a data table. Understanding how SAS works

PeterClemmensen — Mon, 15 Apr 2019 15:50:02 GMT

Hi and welcome to the SAS Communities 🙂

It's not that I don't want to help you with your macro. However, if you really want to understand SAS, I don't think you should compare it directly to R. A SAS data set is not the same think as a matrix. SAS has an entire language called IML (Interactive Matrix Language) that is the closest you get to R in a SAS context. Here, the NROW and NCOL Functions do exist and does basically the same thing as in R.

My recommendation is to take the SAS Programming 1: Essentials course, provided for free by SAS Institute. It will get you going on how the SAS programming language is structured.

If you are a Matrix Kind of Guy/Girl, then check out IML. The book Statistical Programming with SAS/IML® Software by @Rick_SAS is the way to go.

Regards Peter

Re: Macro that counts the number of rows in a data table. Understanding how SAS works

PeterClemmensen — Mon, 15 Apr 2019 15:52:59 GMT

Also, you don't need a macro to do stuff like this 🙂

data _NULL_;
	if 0 then set sashelp.class nobs=n;
	call symputx('nrows',n);
	stop;
run;

%put nobs=&nrows;

Re: Macro that counts the number of rows in a data table. Understanding how SAS works

Rick_SAS — Mon, 15 Apr 2019 16:03:30 GMT

To expand on Draycut's suggestion:

proc iml;
 /* if you want a matrix of numeric vars */
use sashelp.cars;
read all var _num_ into X;
close;

n = nrow(X);
print n;

Re: Macro that counts the number of rows in a data table. Understanding how SAS works

Tom — Mon, 15 Apr 2019 16:25:45 GMT

You cannot treat everything in SAS as if it is a function like you can in R.

In particular SAS macro code is just that, a macro processor, and not a language. It just manipulates text to make it easier to generate the code you want to run.

So if you have a macro that generates a PROC step you cannot "assign" it to a macro variable. Because once the macro call has been converted into the text that it generates instead of running that text as code you will just end up assigning that text to the macro variable.

For example the text that your macro generates starts with:

proc sql; select ...

So if you use that in a %LET statement to set a macro variable:

%let res=%nrow(sashelp.class);

You end up generating this code:

%let res=proc sql;
select ...;

So the macro variable RES now has the string 'proc sql' and the SELECT statement is trying run outside of a PROC SQL step.

If you want to make a macro that return a single value, but requires to run SAS code to do it then write the value to a macro variable. You can ask the caller to tell you want macro variable to create.

%macro nrow(TableName,mvar);
%if not %symexist(&mvar) %then %global &mvar;
proc sql noprint;
  select count(*) into :&mvar trimmed
    from &TableName
  ;
quit; 
%mend;

Then your call would look like:

%nrow(sashelp.class,res);
%put Macro variable RES has value &res;

Re: Macro that counts the number of rows in a data table. Understanding how SAS works

carles — Tue, 16 Apr 2019 06:22:21 GMT

Thank you Peter,

I will start today reading the links you sent me.