Concatenation string with symput

[Burakgns]

Hi,

the current month in which the code is running (11 for this month), the previous month (I want this value to be 10), 2 for the previous month (I want this value to be 9).
I want to bring these values next to the fields under proc sql by assigning symput as m0, m1 and m2 respectively. Like vol_&m0 being vol_11 and vol_&m2 being 9. I would like your help on this issue 🙂

data new;
set one;
m= %sysfunc(month("&sysdate"d));
m_0 = m;
m_1 = m - 1;
m_2 = m - 2;
m0 = put(m_0,BEST12.);
m1 = put(m_1,BEST12.);
m2 = put(m_2,BEST12.);
call symput('m0',m0);
call symput('m1',m1);
call symput('m2',m2);
run;

proc sql;
create table Vol_curr as
select
Vol_&m0,
Vol_&m1,
Vol_&m2,
CURR_&m0,
CURR_&m1,
CURR_&m2
from lib.table;
run;

[PaigeMiller · Accepted solution]

First, I would like to know what happens if you run this code in January 2024. Do you want &m0 to be 1 and &m1 to be 12 and &m2 to be 11? Because otherwise, none of what you have programmed makes any sense. It's easy to get this code to work right in November 2023, but not quite as easy to get it to work right in January 2024.

Next, in your SQL, what happens in January 2024? Do you really want variables VOL_1, VOL_12 and VOL_11? It can be very dangerous to refer to months by their month number instead of some indication of month/year. 

The above issues notwithstanding, here is some code that works in November 2023 and January 2024 — even though it seems pretty meaningless to do it this way.

data _null_;
today = today();
m_0 = month(today);
m_1 = month(intnx('month',today,-1));
m_2 = month(intnx('month',today,-2));
call symputx('m0',m_0);
call symputx('m1',m_1);
call symputx('m2',m_2);
run;

%put &=m0 &=m1 &=m2;

But really, you need to re-think your entire piece of code, including the PROC SQL part, so that it works on ANY month, whether it is January or November.

[Burakgns]

thank you for answer.It is okay for me that &m0 is 1, &m1 is 12 and &m2 is 11 in January 2024 because my source table is filled according to the month passed.

[Tom]

You really shouldn't be using CALL SYMPUT() unless you have an absolute need to generate macro variables with leading and/or trailing spaces in them.  Use the modern (only 30 or more years old?) CALL SYMPUTX() instead.

You really shouldn't be using &SYSDATE.  Is there some reason you don't want to include the century?  Use the 25 year old replacement &SYSDATE9.  And if you want the date at the time the statement runs (as opposed to when the SAS session started) then use the DATE() function instead.

There is no reason to generate the macro variables M0 to M2 over and over and over again.  The values are not changing when different observations are read from ONE.  So just generate them once.

So try:

%let today=%sysfunc(date());
data new;
  set one;
  m= month(&today);
  m_0 = m;
  m_1 = month(intnx('month',&today,-1));
  m_2 = month(intnx('month',&today,-2));
  if _n_=1 then do;
    call symputx('m0',m_0);
    call symputx('m1',m_1);
    call symputx('m2',m_2);
  end;
run;