use ampersand in concatenation string but not a macro variable

[Nasser_DRMCP]

data get_base_url ;
format HTTPS $50. ;
format appli $10. ;
appli = 'GEC' ;
HTTPS = "https://gec0-kf.compagny.fr/CONSULTATION/?mnc=GEC" ;
output ;
run ;

%macro mv_calcul_url(p_appli, p_banque) ;
	%local rc url_definie ;
	%let url_definie = ;
 %let rc=%sysfunc(dosubl(%nrstr(
     proc sql noprint ;
		select
			HTTPS||"&banque="||&p_banque into :url_renvoye
		from get_base_url
		where appli = "&p_appli" 
		;
	quit ;
    ))) ;
	

	&url_renvoye ;

%mend mv_calcul_url;


data get_table_finale1 ;
banque = '14940' ;
output ;
run ;

data want_table_finale2 ;
set table_finale1 ;
url_gec  = "%mv_calcul_url(GEC,banque)";
run ;

hello

i would like to get a url_gec column that contains the string

'https://gec0-kf.compagny.fr/CONSULTATION/?mnc=GEC&banque=14940'

but the log file says that :

WARNING: Apparent symbolic reference BANQUE not resolved.
ERROR: The following columns were not found in the contributing tables: banque.

many thanks in advance for your help

[WarrenKuhfeld]

If you don't want an ampersand to be treated as part of the macro language, enclose it in single quotes not double quotes.

[Quentin]

You'll need some macro quoting to mask the & so that the macro processor doesn't try to resolve the macro variable BANQUE.  

The error about column BANQUE not existing is because your PROC SQL step reads in get_base_url, which doesn't have the BANQUE column in it. 

For this simple example where you have one record in both datasets, the code would work if the SQL step joins get_base_url and get_table_finale1:

data get_base_url ;
format HTTPS $50. ;
format appli $10. ;
appli = 'GEC' ;
HTTPS = "https://gec0-kf.compagny.fr/CONSULTATION/?mnc=GEC" ;
output ;
run ;

%macro mv_calcul_url(p_appli, p_banque) ;
  %local rc url_definie ;
  %let url_definie = ;
  %let rc=%sysfunc(dosubl(%nrstr(
    proc sql noprint ;
      select
        cats(HTTPS,"%nrstr(&banque)=",&p_banque) into :url_renvoye
      from get_base_url,get_table_finale1
      where appli = "&p_appli" 
    ;
    quit ;
    ))) ;
	
	%superq(url_renvoye)

%mend mv_calcul_url;


data get_table_finale1 ;
banque = '14940' ;
output ;
run ;

data want_table_finale2 ;
set get_table_finale1 ;
url_gec  = "%mv_calcul_url(GEC,banque)";
run ;

While I'm a fan of the macro language and DOSUBL, depending on the big picture, it's possible you could get what you want with just a SQL query, without the nee to build a function-style macro, e.g.:

proc sql noprint ;
  create table want as
  select
    banque
   ,cats(HTTPS,"%nrstr(&banque)=",banque)  as url_gec
  from get_base_url,get_table_finale1
  where appli = "GEC" 
;
quit ;

[Tom · Accepted solution]

Let's work through some of your issues.

First if you don't want the macro processor to process the string then use single quotes not double quotes around the string.

'&banque='

Second go ahead and use the appropriate CATxxx() function instead of the plain || operator.  In your code that is important because your dataset variable HTTPS is probably padded with spaces you don't want included in your URL.  

Third since your example dataset does not have a variable named banque I assume you want the value passed into the P_BANQUE parameter to become part of the URL instead.  So you need quotes to make it look like string instead of a variable name.

select cats(HTTPS,'&banque=',"&p_banque") into :url_renvoye

Fourth you do not want to include the semicolon into the result so remove that last semicolon in the macro.

And also you will need protect the value returned by the macro.  So add a call to %SUPERQ() instead of just expanding the macro variable.

%macro mv_calcul_url(p_appli, p_banque) ;
%local rc url ;
%let rc=%sysfunc(dosubl(%nrstr(
proc sql noprint ;
  select cats(HTTPS,'&banque=',"&p_banque")
    into :url
  from get_base_url
  where appli = "&p_appli" 
;
quit;
))) ;

%superq(url)

%mend mv_calcul_url;

You could also use the QUOTE() function to add actual quotes around the value instead by just updating the expression used in the SELECT clause.  With single quotes around the value there is no need the %SUPERQ() call.

quote(cats(HTTPS,'&banque=',"&p_banque"),"'")

And finally since there is a BANQUE variable in the dataset you are using to generate the macro calls then it really looks like the timing of your call is off.  The macro processor will call the macro while the data step is still being compiled.

To use the dataset variable's value when generating the call you need to wait until while the data step is actually running.  You can use RESOLVE() function for that.  Again use one of the CATxxx series of functions to generate the appropriate macro call for each observation.  Remember to use single quotes around the string with the % so the macro processor does not try to run the macro too soon.

data want_table_finale2 ;
  set table_finale1 ;
  url_gec  = resolve(cats('%mv_calcul_url(GEC,',banque,')');
run ;