I have a macro variable that I would like to use as the observation values in a new column in a proc sql table.
The macro variable is &_size, and group&size and above&size are columns in the table 'mydata'. the proc sql creates two columns, and I want a third that has the same value which is the macro variable value.
in a normal proc sql, i would put: ' ' as new-column,
But in a macro, I don't know how to do this. I don't use macros much. below it is ???&_size as mySize . I have tried with single quotes also.
here is what I pass into the macro (which works great without my attempt at creating the new column based on the macro variable:
%sizeEval( sizeLevel=1500, _size=_15 );
Here is the opening of the macro:
%macro sizeEval(sizeLevel=, _size=);
Here is my proc sql in the macro:
create table sustained&_size as
select distinct size, seconds, count(distinct group&_size) as count&_size,
from (select ???&_size as mySize, distinct max(above&_size) as seconds, group&_size
from mydata
where above&_size ^= 0
group by group&_size)
group by seconds, size
order by seconds
Forget whether you have a macro variable or not and write the SAS code you want for one case. Make sure the SAS code works (not sure your posted code will work at all so this is key) before trying to use macro variables and/or macro logic to generate the code.
Are you trying to reference a numeric value? For example if you are just trying to run code like:
select 0.20 as power
and you have a macro variable named FRED that has the value 0.20 then just replace the text with the reference to the macro variable.
select &fred as power
Now if you are trying to generate a character variable then your SAS code probably looks like:
select "CompanyA" as company
now in that case if your macro variable SAM has the value CompanyA then you need to keep the quotes. And they need to be double quote characters and not single quote (aka apostrophe) characters.
select "&sam" as company
because macro triggers like & and % are ignored inside of strings bounded by single quotes.
Forget whether you have a macro variable or not and write the SAS code you want for one case. Make sure the SAS code works (not sure your posted code will work at all so this is key) before trying to use macro variables and/or macro logic to generate the code.
Are you trying to reference a numeric value? For example if you are just trying to run code like:
select 0.20 as power
and you have a macro variable named FRED that has the value 0.20 then just replace the text with the reference to the macro variable.
select &fred as power
Now if you are trying to generate a character variable then your SAS code probably looks like:
select "CompanyA" as company
now in that case if your macro variable SAM has the value CompanyA then you need to keep the quotes. And they need to be double quote characters and not single quote (aka apostrophe) characters.
select "&sam" as company
because macro triggers like & and % are ignored inside of strings bounded by single quotes.
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.