BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
yelkenli
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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.

 

View solution in original post

1 REPLY 1
Tom
Super User Tom
Super User

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.

 

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 1 reply
  • 410 views
  • 0 likes
  • 2 in conversation