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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: