topic Re: Concatenate a string to return the value of a Macro variable in proc SQL in SAS Programming

Concatenate a string to return the value of a Macro variable in proc SQL

GeorgeBonanza — Wed, 17 May 2023 19:07:03 GMT

Below is a simplified example of what I am trying to do. I'd like to know if it is possible to replace the case statement with a concatenation of "DISCOUNT_" and the value of ID and then return the value of that macro variable. For example, the first record of the HAVE dataset would first concatenate "DISCOUNT_" and "001" and then return 0.1, the value of DISCOUNT_001 variable. Essentially, the equivalent of &DISCOUNT_001.

Any insight is appreciated. Thanks in advance.

%let DISCOUNT_001 = 0.1;
%let DISCOUNT_002 = 0.05;

data HAVE;
	input ID $ QTY UNIT;
	datalines;
001 2 500
001 4 500
002 3 300
002 1 300
;
run;

proc sql; create table WANT as
	select ID, SUM(QTY * UNIT * (1 - case ID when "001" then &DISCOUNT_001 when "002" then &DISCOUNT_002 else 0 end)) as REVENUE
	from HAVE
	group by 1;
quit;

Re: Concatenate a string to return the value of a Macro variable in proc SQL

Quentin — Wed, 17 May 2023 20:02:11 GMT

Technically, you could, by SYMGET to resolve the macro variable when the code executes, e.g.:

proc sql; create table WANT as
	select ID, SUM(QTY * UNIT * (1 - input(symget(cats("Discount_",ID)),8.) )) as REVENUE
	from HAVE
	group by 1;
quit;

But this would be an unusual way to do a look-up in SAS. In SQL, you could consider joining on a table with the discount value for each ID. Or another common way to do a lookup in SAS is with a format or informat, e.g.:

proc format ;
  invalue disc
  '001'=.1
  '002'=.05
  ;
run ;

proc sql; create table WANT as
	select ID, SUM(QTY * UNIT * ( 1 - input(ID,disc.) )) as REVENUE
	from HAVE
	group by 1;
quit;

Re: Concatenate a string to return the value of a Macro variable in proc SQL

ballardw — Wed, 17 May 2023 20:02:54 GMT

SYMGET is the function I think you want:

data HAVE;
	input ID $ QTY UNIT;
        z = symget(cats('DISCOUNT_',id));
	datalines;
001 2 500
001 4 500
002 3 300
002 1 300
;
run;

Though may take an extra step to get a numeric value as the result is character

Maybe (as I'm not really sure what you expect as a result)

proc sql; create table WANT as
	select ID, SUM(QTY * UNIT * (1 - input(symget(cats('DISCOUNT_',id)),8.))) as REVENUE
	from HAVE
	group by 1;
quit;

Re: Concatenate a string to return the value of a Macro variable in proc SQL

GeorgeBonanza — Wed, 17 May 2023 20:12:43 GMT

The SYMGET function is what I needed. Another user suggested the same thing just before you did. Thank you for taking the time to reply.