macro to store label

Accepted Solution Solved
Reply
Trusted Advisor
Posts: 1,228
Accepted Solution

macro to store label

Hi experts,

I am trying to store label for each of the five questions in a macro variable named as lab but getting error message that lab is not resolved. Please see the following code and advise how can I correct this. I want to put &lab as title in proc tabulate for each of the five questions (q20a q20c q20d q20e q20f).

%let inputs=q20a q20c q20d q20e q20f;

%macro lab(list_of_vars=,dataset=);

%let NVARS=%sysfunc(countw(&inputs));

%do i = 1 %to &NVARS.;

proc sql;

select label into :lab

from dictionary.columns

where libname=upcase("Work") and memname=upcase("&dataset") and

name=%upcase(%quote(%scan(&list_of_vars,&i)));

quit;

%end;

%lab(list_of_vars=&inputs,dataset=new);

Thanks,

Naeem


Accepted Solutions
Solution
‎07-07-2013 12:41 AM
Super Contributor
Posts: 644

Re: macro to store label

%quote does not wrap a macro value with quote marks.  Look up macro quoting to see the difference.

Your code should work if you have

name="%upcase(%scan(&list_of_vars,&i))"


because macro statements inside double quotes ar resolved.


Richard

View solution in original post


All Replies
Super User
Posts: 10,035

Re: macro to store label

You can use index() or find() to simplify the problem.

%let inputs=q20a q20c q20d q20e q20f;

%macro lab(list_of_vars=,dataset=);

proc sql;

select label into :lab separated by ' '

from dictionary.columns

where libname=upcase("Work") and memname=upcase("&dataset") and

find("&list_of_vars",strip(name));

quit;

%end;

%lab(list_of_vars=&inputs,dataset=new);

Solution
‎07-07-2013 12:41 AM
Super Contributor
Posts: 644

Re: macro to store label

%quote does not wrap a macro value with quote marks.  Look up macro quoting to see the difference.

Your code should work if you have

name="%upcase(%scan(&list_of_vars,&i))"


because macro statements inside double quotes ar resolved.


Richard

Trusted Advisor
Posts: 1,228

Re: macro to store label

Posted in reply to RichardinOz

Thanks Richard for suggesting the solution it is working!

Ksharp - I tried your solution, macro is working but noting in the log when I put %put &lab. Could you please explain it a little bit more?

Thanks,

Naeem


Super User
Super User
Posts: 7,060

Re: macro to store label

The main problem is that you need to define the target macro variable before executing the macro. Otherwise the macro variable will be defined as local and disappear when the macro ends.

%let inputs=q20a q20c q20d q20e q20f;

%let lab=;

%lab(list_of_vars=&inputs,dataset=new);

%put lab=&lab ;

You also need to work on the code in your macro that is attempting to pull the label metadata from the dictionary tables as the current logic will not work for a number of reasons.  You might want to do something like this:

%macro lab(list_of_vars,dataset);

%let lab= ;

proc sql noprint ;

  select quote(trim(coalesce(label,name)))

    into :lab separated by '|'

    from dictionary.columns

    where libname='WORK'

      and memname=%upcase("&dataset")

      and indexw(%upcase("&list_of_vars"),upcase(name))

     order by indexw(%upcase("&list_of_vars"),upcase(name))

  ;

quit;

%mend lab;

For example:

data class;

  set sashelp.class;

  label age='age variable';

run;

%let lab=;

%lab(age sex,class);

%put lab=&lab;

lab="age variable"|"Sex"

Trusted Advisor
Posts: 1,228

Re: macro to store label

Hi Tom,

Thanks for the explanation. Code is very useful and provides new directions for learning. This code seems to concatenate labels for some of the selected variables. What I am trying to do is to save label for one of the variables (q20a q20c q20d q20e q20f) in macro variable and using this macro variable as title in subsequent proc tabulate. In this way I can put label of the question at the top of the table related to a particular question.

Regards,

Naeem

Super User
Super User
Posts: 7,060

Re: macro to store label

So you want to take any of the labels for those variables?

One way is to just take the first one that has a value.

%let lab= ;

proc sql noprint ;

  select label

    into :lab

    from dictionary.columns

    where libname='WORK'

      and memname=%upcase("&dataset")

      and indexw(%upcase("&list_of_vars"),upcase(name))

      and label is not null

  ;

quit;

But then SAS will pad the macro variable with trailing blanks.  If you are using 9.3 you can add the TRIMMED keyword to the INTO clause.

Otherwise you could just add another %LET statement.

%let lab=&lab ;

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 322 views
  • 6 likes
  • 4 in conversation