topic Re: Create a macro variable list with quoted values in SAS Programming

Create a macro variable list with quoted values

vanja — Thu, 29 Nov 2018 09:43:32 GMT

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);

Re: Create a macro variable list with quoted values

RW9 — Thu, 29 Nov 2018 09:55:07 GMT

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.

Re: Create a macro variable list with quoted values

vanja — Thu, 29 Nov 2018 11:57:58 GMT

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.

Re: Create a macro variable list with quoted values

PaigeMiller — Thu, 29 Nov 2018 12:02:12 GMT

I don't think you need macro variables here.

axis2 label=none minor=none value=("01jan2002"d to "01jan2019"d by year);

Re: Create a macro variable list with quoted values

vanja — Thu, 29 Nov 2018 12:34:00 GMT

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.

Re: Create a macro variable list with quoted values

Quentin — Thu, 29 Nov 2018 12:36:02 GMT

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.

Re: Create a macro variable list with quoted values

vanja — Thu, 29 Nov 2018 12:39:57 GMT

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";

Re: Create a macro variable list with quoted values

PaigeMiller — Thu, 29 Nov 2018 13:12:57 GMT

@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.

Re: Create a macro variable list with quoted values

PaigeMiller — Thu, 29 Nov 2018 13:14:20 GMT

@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.

Re: Create a macro variable list with quoted values

Quentin — Thu, 29 Nov 2018 13:57:12 GMT

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).

Re: Create a macro variable list with quoted values

Tom — Thu, 29 Nov 2018 14:26:49 GMT

@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;

Re: Create a macro variable list with quoted values

vanja — Thu, 29 Nov 2018 14:36:10 GMT

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.

Re: Create a macro variable list with quoted values

vanja — Thu, 29 Nov 2018 14:38:26 GMT

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.

Re: Create a macro variable list with quoted values

vanja — Thu, 29 Nov 2018 14:39:59 GMT

You are absolutely right, I am a bit uncustomed to ask for help in forums. Next time I will do better.