Hi!
I would like to create a macro or a macro variable that returns the following list:
"2002" "2003" "2004" "2005" "2006" "2007" "2008" "2009" "2010" "2011" "2012" "2013" "2014" "2015" "2016" "2017" "2018" "2019"
The goal is to produce a value list in an axis statment for proc plot:
This code produces a list but still does not work in the axis statement and preferably I would like a macro that returns the list I am after. But I would also be happy to just get a macro variable with a list that works.
data valuesYear1;
do year=2002 to 2019;
qYear=quote(compress(year));
output;
end;
run;
proc sql noprint;
select qyear into :valuesYear1 separated by ' '
from valuesYear1;
quit;
axis2 label=none minor=none order=("01jan2002."d to "01jan2019."d by 365.25) value=(&valuesAr1);
First off, drop proc plot. It is very old and has, for a long time, been replaced by sgplot for more functionality and easier use.
Second, creating a list of values like that in a macro variable isn't generally a good idea. First off, do you not want those items to be numeric? That way you can just specify the range like:
proc sgplot...; xaxis values=(2002 to 2019); ... run;
Much easier, and less coding.
I don't have enough knowledge (or time) to transfer the graphs from proc sgplot to proc gplot and also, there are so many of them in the code.
Tried with value=(2002 to 2019) in proc gplot but seems not to be supported.
So the only thing that seem to work with the current code is to acutally specfiy the value list and I need help in creating that macro or that macro variable.
I'm confused. It looks to me like the code you posted creates the list you want:
8 proc sql noprint; 9 select qyear into :valuesYear1 separated by ' ' 10 from valuesYear1; 11 quit; 12 13 %put &valuesYear1 ; "2002" "2003" "2004" "2005" "2006" "2007" "2008" "2009" "2010" "2011" "2012" "2013" "2014" "2015" "2016" "2017" "2018" "2019"
Maybe that's not actually the list you need. Maybe try hardcoding the list into your axis statement, and get that working. Then you can go back to making a macro or macro variable that will generate the list.
You are absolutely right. The code looks ok to me too but the macro variable it produces does not work in the value statement.
However, this macro variable works. But I would so much like it to by dynamic.
%let valuesAr1="2002" "2003" "2004" "2005" "2006" "2007" "2008" "2009" "2010" "2011" "2012" "2013" "2014" "2015" "2016" "2017" "2018" "2019";
@vanja wrote:
You are absolutely right. The code looks ok to me too but the macro variable it produces does not work in the value statement.
However, this macro variable works. But I would so much like it to by dynamic.
%let valuesAr1="2002" "2003" "2004" "2005" "2006" "2007" "2008" "2009" "2010" "2011" "2012" "2013" "2014" "2015" "2016" "2017" "2018" "2019";
Here's an important rule. You can't say "does not work". That does not give any information that would be helpful to solve the problem. You need to show us the LOG where you see the error, or show us the output that is not correct, or both.
You are absolutely right, I am a bit uncustomed to ask for help in forums. Next time I will do better.
Well, I'm flummoxed. From what I can see, your hard-coded list is exactly the same as the list generated from the SQL step. I can't see how/why you would get different results. If you're getting errors, please post your log. Or better yet, post a full example which replicates the problem.
Here is my log from generating the list, and comparing it to the hardcoded list to confirm they are the same:
16 data valuesYear1; 17 do year=2002 to 2019; 18 qYear=quote(compress(year)); 19 output; 20 end; 21 run; NOTE: Numeric values have been converted to character values at the places given by: (Line):(Column). 18:24 NOTE: The data set WORK.VALUESYEAR1 has 18 observations and 2 variables. 22 23 proc sql noprint; 24 select qyear into :valuesYear1 separated by ' ' 25 from valuesYear1; 26 quit; 27 28 %let valuesAr1="2002" "2003" "2004" "2005" "2006" "2007" "2008" "2009" "2010" "2011" "2012" 28 ! "2013" "2014" "2015" "2016" "2017" "2018" "2019"; 29 30 %put >>%eval(&valuesYear1=&valuesAr1)<< ; >>1<<
If your real data has some macro quoting going on, you can try %unquote(&valuesYear1).
Thank you for the %eval-code. It made me realize that I was right from the beginning.
%put >>%eval(&valuesYear1=&valuesAr1)<< ;
The mistake I made when I wrote "that it didn't work" must have been that I wrote values=("&valuesYear1"), that is I wrote it within quotes. What a stupid mistake. Now my original code actually works.
@vanja wrote:
You are absolutely right. The code looks ok to me too but the macro variable it produces does not work in the value statement.
However, this macro variable works. But I would so much like it to by dynamic.
%let valuesAr1="2002" "2003" "2004" "2005" "2006" "2007" "2008" "2009" "2010" "2011" "2012" "2013" "2014" "2015" "2016" "2017" "2018" "2019";
If the macro variable values are the same the code should run the same. If they don't then either there are other changes to the code or the macro variable values are NOT the same.
data _null_;
do year=2002 to 2019;
length str $200 ;
str=catx(' ',str,quote(put(year,4.)));
end;
call symputx('valuesAr1',str);
run;
I don't think you need macro variables here.
axis2 label=none minor=none value=("01jan2002"d to "01jan2019"d by year);
do you mean order instead of value?
axis2 label=none minor=none order=("01jan2002"d to "01jan2019"d by year);
The statement above with order= works in a way but I get som ugly warnings (which I don't like)
WARNING: The intervals on the axis labeled "DATEVAR" are not
evenly spaced.
WARNING: No minor tick marks will be drawn because major tick
increments have been specified in uneven or unordered
intervals.
@vanja wrote:
do you mean order instead of value?
axis2 label=none minor=none order=("01jan2002"d to "01jan2019"d by year);
The statement above with order= works in a way but I get som ugly warnings (which I don't like)
WARNING: The intervals on the axis labeled "DATEVAR" are not evenly spaced. WARNING: No minor tick marks will be drawn because major tick increments have been specified in uneven or unordered intervals.
I guess these don't bother me at all. All of the January 1 of each year is not evenly spaced once you include a leap year.
Thank you so much for trying to help me. When I finally got the macro code to work I stick with my original solution. But I am so grateful for the help and for explaining to me why I got that warning message. Did not at all understand it was because of leap years.
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.