topic Re: SAS macro for counting the number of variables/columns in a dataset in SAS Programming

SAS macro for counting the number of variables/columns in a dataset

smilingmelbourne — Sat, 30 Apr 2011 09:26:59 GMT

Hi everyone, I can't figure out why this code doesn't work. Specificially, the &varnum doesn't get resolved.

options symbolgen;
options mprint;
options mlogic;
%macro count_vars(dataset);
%local varnum;
proc sql;
select count(*) into : varnum
from dictionary.columns
where memname=upcase("&dataset");
quit;
%mend count_vars;

%count_vars(Mf_hist_holdings_all)
%put varnum = &varnum;
The ERROR message is like below:

"MLOGIC(COUNT_VARS): Ending execution.
WARNING: Apparent symbolic reference VARNUM not resolved.
421 %put varnum = &varnum;
varnum = &varnum"

By the way, I don't understand why this code doesn't run.

%macro count_vars(dataset, varnum);
proc sql;
select count(*) into : varnum
from dictionary.columns
where memname=upcase("&dataset");
quit;
%mend count_vars;

%count_vars(Mf_hist_holdings_all, varnum)

The reason is I don't want to remember that varnum is a variable that contains the number of variables in a dataset. Imagine I have 100 macros and everytime I run one such macro, I have to open the source code and check what name that variable has, i.e. here varnum. So when I clean a dataset, I can do the following

%global myvars;
%count_vars(mydataset, myvars)

INSTEAD OF openning the source code and checking that the name is varnum and only then can I put "varnum" as an argument to the macro %count_vars.

Can somebody please advise?

Message was edited by: smilingmelbourne Message was edited by: smilingmelbourne

Re: SAS macro for counting the number of variables/columns in a dataset

art297 — Sat, 30 Apr 2011 13:23:42 GMT

Your first question is easy: you defined varnum as local!

If you add a %put statement within the macro you will see that it does, in fact, get resolved. E.g.,
[pre]

%macro count_vars(dataset);
%local varnum;
proc sql;
select count(*) into : varnum
from dictionary.columns
where memname=upcase("&dataset")
;
quit;
%put varnum = &varnum;
%mend count_vars;

%count_vars(class)
%put varnum = &varnum;
[/pre]
HTH,
Art

Re: SAS macro for counting the number of variables/columns in a dataset

Patrick — Sun, 01 May 2011 08:16:27 GMT

Hi
You probabely should amend your where clause in order to avoid issues for cases where you have a table with the same name in more than one library.

where libname=%upcase("%scan(work.&dataset,-2)") and memname=%upcase("%scan(&dataset,-1)")

HTH
Patrick P.S: What's the story of your name?
http://www.kitezh.com/texts/melbsmile.htm :-))

Message was edited by: Patrick

Re: SAS macro for counting the number of variables/columns in a dataset

Ksharp — Tue, 03 May 2011 03:34:01 GMT

If you want the number of variables in a dataset.then use dictionary.tables, in it there is column named nvar is what you need.

Ksharp

Re: SAS macro for counting the number of variables/columns in a dataset

smilingmelbourne — Tue, 03 May 2011 08:53:43 GMT

Thank you all very much for the help. I've learnt a lot.