macro variable value as observation value in a new column proc sql create table

yelkenli — Thu, 30 Jan 2020 23:05:03 GMT

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

Re: macro variabl a

Tom — Thu, 30 Jan 2020 23:09:47 GMT

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.

topic Re: macro variabl a in SAS Programming

macro variable value as observation value in a new column proc sql create table

Re: macro variabl a