topic Re: Create List of Dates automatically each month in SAS Programming

Create List of Dates automatically each month

pangea17 — Sat, 10 Dec 2016 01:00:13 GMT

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!

Re: Create List of Dates automatically each month

Reeza — Sat, 10 Dec 2016 01:09:37 GMT

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;

Re: Create List of Dates automatically each month

Patrick — Sat, 10 Dec 2016 01:38:02 GMT

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

Re: Create List of Dates automatically each month

pangea17 — Sat, 10 Dec 2016 18:44:23 GMT

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;

Re: Create List of Dates automatically each month

mkeintz — Sat, 10 Dec 2016 23:09:38 GMT

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;

Re: Create List of Dates automatically each month

pangea17 — Mon, 12 Dec 2016 19:09:06 GMT

data _null_;
25 do Y=1 to 28;
26 Z=Y-1;
27 call symput(cats('M',Y),put(intnx('month',today(),-1,'b')+Y,date9.));
28 call symput(cats('E',Z),put(intnx('month',today(),0,'b')+Z,date9.));
29 end;
30 run;

NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

31
32
33
34 proc sql;
35 select m1, m2, m3, m4, m5, m6, m7, m8, m9, m10, m11, m12, m13, m14, m5, m16, m17, m18, m19, m20,
36 m21, m22, m23, m24, m25, m26, m27, m28, e1, e2, e3, e4, e5, e6, e7, e8, e9, e10, e11, e12, e13,
37 e14, e15,e16, e17, e18, e19, e20, e21, e22, e23, e24, e25, e26, e27, e28
38 into
39 :m1, :m2, :m3, :m4, :m5, :m6, :m7, :m8, :m9, :m10, :m11, :m12, :m13, :m14, :m15, :m16, :m17, :m18, :m19, :m20,
40 :m21,:m22, :m23, :m24, :m25, :m26, :m27, :m28, :e1, :e2, :e3, :e4, :e5, :e6, :e7, :e8, :e9, :e10, :e11, :e12,
41 :e13, :e14, :e15, :e16, :e17, :e18, :e19, :e20, :e21, :e22, :e23, :e24, :e25, :e26, :e27, :e28
42 from _null_;
ERROR: Table WORK._NULL_ doesn't have any columns. PROC SQL requires each of its tables to have at least 1 column.
ERROR: The following columns were not found in the contributing tables: e1, e10, e11, e12, e13, e14, e15, e16, e17, e18, e19, e2,
e20, e21, e22, e23, e24, e25, e26, e27, e28, e3, e4, e5, e6, e7, e8, e9, m1, m10, m11, m12, m13, m14, m16, m17, m18, m19,
m2, m20, m21, m22, m23, m24, m25, m26, m27, m28, m3, m4, m5, m6, m7, m8, m9.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
43 QUIT;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
2 The SAS System 08:30 Monday, December 12, 2016

cpu time 0.00 seconds

Re: Create List of Dates automatically each month

Reeza — Mon, 12 Dec 2016 19:24:58 GMT

@pangea17 Is there a question in there somewhere?

Re: Create List of Dates automatically each month

pangea17 — Mon, 12 Dec 2016 19:27:54 GMT

This code does not work. The output I got is listed below.

Re: Create List of Dates automatically each month

Reeza — Mon, 12 Dec 2016 20:18:06 GMT

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;

Re: Create List of Dates automatically each month

ballardw — Mon, 12 Dec 2016 22:22:44 GMT

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