DATA Step, Macro, Functions and more

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

Reply
Contributor
Posts: 73

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

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
PROC Star
Posts: 7,486

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

Posted in reply to smilingmelbourne
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
Respected Advisor
Posts: 4,173

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

Posted in reply to smilingmelbourne
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
Super User
Posts: 10,041

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

Posted in reply to smilingmelbourne
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
Contributor
Posts: 73

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

Posted in reply to smilingmelbourne
Thank you all very much for the help. I've learnt a lot.
Ask a Question
Discussion stats
  • 4 replies
  • 2457 views
  • 0 likes
  • 4 in conversation