So, I am trying to create a two lists of dates that change each month. I want to generate for instance Nov 2nd to Nov 29th and Dec 1st to Dec 28. The following month it would be Dec 2nd to Dec 29th and Jan 1st to Jan 28th. These dates need to go into a data step that will create some variables based on these dates. It would be tedious to manually change this every month, so I want to use a macro.
%MACRO month (Y,Z);
data _null_;
call symputx('M', PUT(intnx('DAY',intnx('MONTH',%SYSFUNC(today()),-1,'b'),&Y), date9.));
call symputx('E', PUT(intnx('DAY',intnx('MONTH',%SYSFUNC(today()),0,'b'),&Z), date9.));
RUN;
%MEND;
%month(1,0); %month(2,1); %month(3,2); %month(4,3); %month(5,4); %month(6,5); %month(7,6);
%month(8,7); %month(9,8); %month(10,9); %month(11,10); %month(12,11); %month(13,12); %month(14,13);
%month(15,14); %month(16,15); %month(17,16); %month(18,17); %month(19,18); %month(20,19); %month(21,20);
%month(22,21); %month(23,22); %month(24,23); %month(25,24); %month(26,25); %month(27,26); %month(28,27);
proc sql;
select m1, m2, m3, m4, m5, m6, m7, m8, m9, m10, m11, m12, m13, m14, m5, m16, m17, m18, m19, m20,
m21, m22, m23, m24, m25, m26, m27, m28, e1, e2, e3, e4, e5, e6, e7, e8, e9, e10, e11, e12, e13,
e14, e15,e16, e17, e18, e19, e20, e21, e22, e23, e24, e25, e26, e27, e28
into
:m1, :m2, :m3, :m4, :m5, :m6, :m7, :m8, :m9, :m10, :m11, :m12, :m13, :m14, :m15, :m16, :m17, :m18, :m19, :m20,
:m21,:m22, :m23, :m24, :m25, :m26, :m27, :m28, :e1, :e2, :e3, :e4, :e5, :e6, :e7, :e8, :e9, :e10, :e11, :e12,
:e13, :e14, :e15, :e16, :e17, :e18, :e19, :e20, :e21, :e22, :e23, :e24, :e25, :e26, :e27, :e28
from _null_;
QUIT;
My problem is that M and E will be overwritten each time. And SAS will not allow you to have a macro variable name for the 'M' or 'E' in the symputx statements. Otherwise I would just have it read 'M&Y' and 'E&Z' and it would work. Am I going about this the wrong way, or is there a better way to make this work?
Using Enterprise guide 9.4 and SAS 9.1.
Thanks everyone for your help!
The code provided works, there's no errors for that portion of the code.
CALL SYMPUT creates macro variables. If you check, you'll see your macro variables already exist.
Your proc sql step is not required. I suggested this method, but did not provide code for this solution, I'm including it below the other code below. I've added comments to help you understand what's happening in the code. If you have questions, feel free to ask.
Also, a data _null_ step does not create a data set that you could use to query. The only possible modification I could suggest would be to change the CALL SYMPUT to CALL SYMPUTX.
data _null_;
do Y=1 to 28; *create indexes for your dates, M series of macro variables;
Z=Y-1; *create indexes for your dates, E series of macro variables;
*create M macro variable for for value of Y;
call symputx(cats('M',Y),put(intnx('month',today(),-1,'b')+Y,date9.));
*create E macro variable for value of Z;
call symputx(cats('E',Z),put(intnx('month',today(),0,'b')+Z,date9.));
end;
run;
Using a data step:
data dates;
do Y=1 to 28; *create indexes for your dates, M series of macro variables;
Z=Y-1; *create indexes for your dates, E series of macro variables;
*create M macro variable for for value of Y;
call symputx(cats('M',Y),put(intnx('month',today(),-1,'b')+Y,date9.));
*create E macro variable for value of Z;
call symputx(cats('E',Z),put(intnx('month',today(),0,'b')+Z,date9.));
M=intnx('month',today(),-1,'b')+Y;
E=intnx('month',today(),0,'b')+Z;
output;
format e m date9.;
end;
run;
proc sql noprint;
select m into :m1-
from dates;
select e into :e1-
from dates;
quit;
%put &m1;
%put &m2;
%put &m28;
%put &e1;
%put &e15;
%put &e28;
1. Create a dataset with the values that you want - not macro variables. I don't understand your rules for creating the date so I'll wait for clarification on that, if you want code.
2. Use SQL to create the consecutive macrovariables you want
3. Then once you've solved this issue (learning/practice) redesign your code to not use 60 macro variables. There has to be a better way...
Step 2:
proc sql noprint;
select m into :m1-
from data_from_step1;
quit;
Step 3: possibly...
proc sql;
create table want as
select *
from a
where date in (select m from data_from_step1);
quit;
I am new to using macros so I had to do some research. I think I found a solution. You have to use a global statement before the data step and put double quotes around the variable name after the symputx statement. Thanks for your help.
%MACRO month (Y,Z);
%global M&Y E&Z;
data null;
call symputx("M&Y", PUT(intnx('DAY',intnx('MONTH',%SYSFUNC(today()),-1,'b'),&Y), date9.));
call symputx("E&Z", PUT(intnx('DAY',intnx('MONTH',%SYSFUNC(today()),0,'b'),&Z), date9.));
RUN;
%MEND;
If feels that you're going way too complicated about this.There are calendar functions which might help to come up with a simpler approach.
If you give us the bigger picture and can explain what you have and what you want to achieve, then may-be we can offer something less complicated.
Ideally you provide a data step creating some "have" sample data and then describe the desired output (not only the tables with dates but describe the whole problem you want to solve, so how you're actually using these dates).
Regardless of how you do this (in a data step, or in a macro), you should do it in a loop, which would go from Y=1 to 28 and synchroniously from Z=0 to 27.
I think you're far better off in a data step loop than a macro loop. It will NOT require any use of a macro function (such as %sysfunc). Also note the adding, say 5, to an INTNX function advance the date by exactly 5 days. So there is no need for the "INTNX(date" usage. Just add Y or Z.
data _null_;
do Y=1 to 28;
Z=Y-1;
call symput(cats('M',Y),put(intnx('month',today(),-1,'b')+Y,date9.));
call symput(cats('E',Z),put(intnx('month',today(),0,'b')+Z,date9.));
end;
run;
@pangea17 Is there a question in there somewhere?
This code does not work. The output I got is listed below.
The code provided works, there's no errors for that portion of the code.
CALL SYMPUT creates macro variables. If you check, you'll see your macro variables already exist.
Your proc sql step is not required. I suggested this method, but did not provide code for this solution, I'm including it below the other code below. I've added comments to help you understand what's happening in the code. If you have questions, feel free to ask.
Also, a data _null_ step does not create a data set that you could use to query. The only possible modification I could suggest would be to change the CALL SYMPUT to CALL SYMPUTX.
data _null_;
do Y=1 to 28; *create indexes for your dates, M series of macro variables;
Z=Y-1; *create indexes for your dates, E series of macro variables;
*create M macro variable for for value of Y;
call symputx(cats('M',Y),put(intnx('month',today(),-1,'b')+Y,date9.));
*create E macro variable for value of Z;
call symputx(cats('E',Z),put(intnx('month',today(),0,'b')+Z,date9.));
end;
run;
Using a data step:
data dates;
do Y=1 to 28; *create indexes for your dates, M series of macro variables;
Z=Y-1; *create indexes for your dates, E series of macro variables;
*create M macro variable for for value of Y;
call symputx(cats('M',Y),put(intnx('month',today(),-1,'b')+Y,date9.));
*create E macro variable for value of Z;
call symputx(cats('E',Z),put(intnx('month',today(),0,'b')+Z,date9.));
M=intnx('month',today(),-1,'b')+Y;
E=intnx('month',today(),0,'b')+Z;
output;
format e m date9.;
end;
run;
proc sql noprint;
select m into :m1-
from dates;
select e into :e1-
from dates;
quit;
%put &m1;
%put &m2;
%put &m28;
%put &e1;
%put &e15;
%put &e28;
@pangea17 wrote:
This code does not work. The output I got is listed below.
No output would be generated for the data _null_ step.
This will show the macro variables created and their values:
data _null_; do Y=1 to 28; Z=Y-1; call symputx(cats('M',Y),put(intnx('month',today(),-1,'b')+Y,date9.)); call symputx(cats('E',Z),put(intnx('month',today(),0,'b')+Z,date9.)); end; run; %put _user_;
(plus any other macro variables you have declared ...)
Most of the time working with generated dates there really isn't a good reason to use the date9 or other format unless you are going to have a human read them. If you leave them as the numeric value then you need not do anything like "&M5"d to have them treated as dates elsewhere in code, especially in a data step or sql comparison or assignment.
If I'm using them as part of a title that people will read, then date9 or such is appropriate.
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 16. 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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: