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;
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;
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.