Proc SQL into: macro variable not resolving inside a macro

[constliv]

I have the following listing of dataset names and thier dataset labels in a dataset callef "listfiles" on SAS EG v 5.1 :

memname	memlabel
CHECKING
LISTFILES
NOV	Not sorted data
NOV_1	Nov_1 dataset that is sorted
SORTED_NOV
T
_PRODSAVAIL

The formats of both columns are length of 32 bytes.

I am writing a macro to retrieve the dataset label giving the dataset name as a parameter, for the purpose of using the dataset lable as title in proc print. I wrote the following code:

options symbolgen ;

%macro label(memname=) ;
%global &titleone   ;
proc sql;
select trim(memlabel) into :"&titleone." length = 256.
from listfiles 
where trim(memname) = &memname ;
quit;
%mend label ;

%label (memname= "NOV" ) ;

%put  &titleone ;

But I keep getting the error:

SYMBOLGEN: Macro variable TITLEONE resolves to Not sorted data

SYMBOLGEN: Macro variable TITLEONE resolves to Not sorted data

 NOTE: Line generated by the macro variable "TITLEONE".

32 "Not sorted data

_________________________________________________________________________________________________________________________

22

76

SYMBOLGEN: Macro variable MEMNAME resolves to "NOV"

ERROR 22-322: Expecting a name.

ERROR 76-322: Syntax error, statement will be ignored.

Appreciate help on fixing this. I think it must be something to do with macro quoting....but I am not able to nail it.

[andreas_lds]

In the into-clause in proc sql you have to use the name of the macro variable without ampersand and quotes. 

options symbolgen ;

%macro label(memname=) ;
  %global &titleone   ;

  proc sql noprint;
    select trim(memlabel) into :titleone trimmed
    from listfiles 
    where trim(memname) = "&memname";
  quit;
%mend label ;

%label (memname= NOV ) ;

%put  &titleone ;

[Tom · Accepted solution]

Is the name of the macro variable you want to populate TITLEONE? Or does the macro variable named TITLEONE contain the name that you want to use for the macro variable that you are going to create?  I would assume that you want the former so you need to change the %GLOBAL statement to:

%global titleone;

Then in the SELECT statement you will also just use the name of the variable.

To trim the results of the select when writing into the macro variable use the TRIMMED keyword of the INTO syntax .

select memlabel into :titleone trimmed

In your WHERE clause  to compare your dataset variable to a character literal. Normally you do NOT include quotes in macro variable values so you should add the quotes when they are needed where you are using the macro variable's value.

where memname = "&memname"

You also might want to make sure to account for case issue.  What if the user used Nov instead of NOV? In SAS code like the DATA= option on PROC PRINT the case wouldn't matter.  But when comparing character strings "Nov" and "NOV" are different values.

What do you want TITLEONE to have when the member name is not found?

Also what did you want to use for those members that do not have labels?

You probably want something like this.

%macro label(memname=) ;
%global titleone ;
%let titleone=&memname ;
proc sql noprint;
select coalesce(memlabel,memname) into :titleone trimmed
  from listfiles
  where upcase(memname) = %upcase("&memname")
;
quit;
%mend label ;

%let dsname=nov;
%label (memname= &dsname) ;
Title1 "PROC PRINT for &titleone";
proc print data=&dsname ;
run;

[constliv]

Tom,

Well done ! Thank you for the detailed response, which is so helpful and I am sure other readers will also benefit.

I liked the defensive programming aspects you brought into the solution and how you made the name of the dataset also a macro variable. Good learning points. Speaking from the other side though, sometimes managers want quick and dirty results and programmers are forced to skip features that will give robust results for want of time.

One small thing. I had to tweak the coalesce part of the code to print the dataset label as title if populated, the order of the arguments of the coalesce function had to be swapped as follows (memlabel first and then memname). Otherwise it was not picking up the dataset label as titile for the print output:

%macro label_1(memname=) ;
%global titleone ;
%let titleone=&memname ;
proc sql noprint;
select coalesce(memlabel, memname) into :titleone trimmed
  from listfiles
  where upcase(memname) = %upcase("&memname")
;
quit;
%mend label_1 ;

%let dsname=nov_1;
%label_1 (memname= &dsname) ;
%put &titleone ;
Title1 "PROC PRINT for &titleone";
proc print data=&dsname ;
run;

Thank you.

[constliv]

Hi Tom,

You may want to to edit the coalesce part so that tehcnically the code is indeed the final solution.

[Tom]

---

@constliv wrote:
Hi Tom,

You may want to to edit the coalesce part so that tehcnically the code is indeed the final solution.

---

Updated.

[constliv]

Hi andreas_ids,

Your solution worked. Thank you.

And when I extended your solution to print titles, which is the goal of the program, I found that the below code works :

options symbolgen ;

%macro label(memname=) ;
  %global &titleone   ;

  proc sql noprint;
    select trim(memlabel) into :titleone trimmed
    from listfiles 
    where trim(memname) = "&memname";
  quit;
%mend label ;

%label (memname= NOV ) ;

title 'PROC PRINT for' &titleone  ;
proc print data = nov ;
run ;

Not sure if the community board allows two responses to be selected as 'Accepted as solution' as I would like to accept Tom's solution as well.

Thank you for taking time to help. Much appreciated.