date macro variables

[sameer112217]

I have an SQL Program like with field names

Proc SQL;

Select var1,

           var2,

           var3,

           31JAN2017,

           28FEB2017,

           31MAR2017,

           30APR2017,

           31MAY2017

from table1;

quit;

Lets assume I am currently in the month of June2017 and my reporting month is May2017, if my month changes to Jul 2017, I need to add 30JUN2017 as a variable in the table. Instead of adding mannually I need to automate using macro. I tried this below

 %macro loop;                                                                                                                                                                                     
 %do i=0 %to 11;                                                                                                                                                                                
 %let date1=%sysfunc(intnx(month, '01JAN2017'd, &i. ,e), date9.);                                                                                                                                 
 %put &date1.;                                                                                                                                                                                   
 %end;                                                                                           
 %mend;                                                                                                                                                                                              
 %loop;

But it just added values in the log. How can I create macro to add the variable automatically from 31JAN2017 to reporting month. Similarly if the month is JAN 2017 I need it from 31JAN2016 to 31DEC2016 as variables to be added in the select table variables

31JAN2017                                                                                            
28FEB2017                                                                                            
31MAR2017                                                                                            
30APR2017                                                                                            
31MAY2017                                                                                            
30JUN2017                                                                                            
31JUL2017                                                                                            
31AUG2017                                                                                            
30SEP2017                                                                                            
31OCT2017                                                                                            
30NOV2017                                                                                            
31DEC2017  

[sameer112217]

Similary I have one transpose table statement just similar to abovewhere I need to add '31JAN2017'd, '28FEB2017'd till reporting month end date. How can I write a macro to do that

[ChrisNZ]

What will those newly created columns contain? In other words, why create an empty column?

I would be a lot better to have a vertical table with column called DATE, and then you can transpose it if needed.

[sameer112217]

You slightly got it wrong. Please see the PROC SQL. I am not creating tables just selecting variables. That table has value till 31DEC2017. I now need to choose variable from that one month prior to current month which is reporting month

[Astounding]

Step 1:  make the 11 data driven

%do i = 0 %to 11;

Controlling the endpoint depends on what is being used for that purpose (system date vs. a value supplied to the macro).  Here's a way to use the system date:

%do i = 0 %to %sysfunc(month("sysdate9"d)) - 1;

You can fiddle with it to see whether it's correct to subtract 1 at the end of the formula, but that looks right.

Step 2:  generate a part of the SELECT statement instead of writing messages to the log

Slight changes to the macro (and possibly where igets used can do that).  This statement gets changed:

%put &date1.;

Instead it becomes:

, &date1.

That looks a little funny the first time you see it, but that generates text which forces SAS to use that text as part of the program.  So the macro to generate the text needs to be added in the right place:

Select var1, var2, var3

    %call_the_macro_here

    from table1;

Note there is no comma after VAR3, since the macro supplies that comma.

[sameer112217]

Thanks for the detail step but it did not work out because first do look statement is 0 to 11 and other is month function which is not number it is throwing error. Also i need comma after months in variable. thanks anyways for effort.

[Astounding]

What you are describing is different than what I posted.  For example, there is supposed to be a comma before &date1. as part of the macro definition.

Show what you actually ran, and it can be fixed.

[Reeza]

 There's the small problem of dates not being valid column names. It may be easier to use a DROP/KEEP statement in a data step. What does the master table look like?

[sameer112217]

Reeza the master tables have other character variables. But I need these month variable like 31JAN2017 to reporting month which will have amount for that particular month. If reporting month is July I need the variables till 30JUN2017 to be automatically added after those already present variables. Even if you show me you can remove those with keep and drop i will be fine but there should be no physical intervention of adding manually everytime. The reason is it is appearing in more than 100 places in different jobs. I need to automated. If it can be done with data step and not by proc sql i am fine but i need exact sas code please. Thanks

[Tom · Accepted solution]

You cannot have a VARIABLE that is named 31JAN2017.  You could possible get away with it if you set VALIDVARNAME=ANY, but even then you would need to use a name literal like '31JAN2017'n to refer to it. 

Sounds like you want to generate year to date variables (all months of the year up to the previous month).  So when current month is January you want the 12 months from last year.  So here is simple macro to generate a list of names like that.

%macro datelist(currentdt=%sysfunc(date(),date9));
%local firstdt nmonths i date;
%let firstdt=%sysfunc(intnx(year,"&currentdt"d,0-(1=%sysfunc(month("&currentdt"d)))),date9);
%let nmonths=%eval(%sysfunc(intck(month,"&firstdt"d,"&currentdt"d)) - 1);
%do i=0 %to &nmonths;
  %let date=%sysfunc(intnx(month,"&firstdt"d,&i,e),date9);
  ,"&date"n
%end;
%mend;

So if you call it with no input date it uses the current date.

%put %datelist;
,"31JAN2017"n   ,"28FEB2017"n   ,"31MAR2017"n   ,"30APR2017"n   ,"31MAY2017"n   ,"30JUN2017"n

Or you can give it some other date. Let's try one in January.

%put %datelist(currentdt=01JAN2017);
,"31JAN2016"n   ,"29FEB2016"n   ,"31MAR2016"n   ,"30APR2016"n   ,"31MAY2016"n   ,"30JUN2016"n
,"31JUL2016"n   ,"31AUG2016"n   ,"30SEP2016"n   ,"31OCT2016"n   ,"30NOV2016"n   ,"31DEC2016"n

So now you SQL code looks like this.

select var1
     , var2
     , var3
     %datelist()
from table1
;

\ 

[sameer112217]

Tom thanks for the detailed code. That was very high quality stuff. I got it right all the code except for the SQL part which is throwing error... how can i eliminate the error in PROC SQL step. In the SQL there is issue on invoking it.

264  %put %datelist;
,"31JAN2017"n  ,"28FEB2017"n  ,"31MAR2017"n  ,"30APR2017"n  ,"31MAY2017"n  ,"30JUN2017"n
265  Proc SQL;
          ---
          13
266  Select age,
     ------
     180
ERROR 13-12: Unrecognized SAS option name, SQL.
ERROR 180-322: Statement is not valid or it is used out of proper order.
267         %datelist()
268         from sashelp.class;

269         quit;

270  Proc SQL;
271  Select age,
272         %datelist()
NOTE 137-205: Line generated by the invoked macro "DATELIST".
1            ,"&date"n
             -
             22
ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string,
              a numeric constant, a datetime constant, a missing value, *, BTRIM, INPUT, LOWER, PUT,
              SUBSTRING, UPPER, USER.

273         from sashelp.class;
274         quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds

[Tom]

The first error is probably because you did not end the statement before PROC SQL with a semi-colon.

The second error is because you added an extra comma before the macro call. You could make the macro smart enough not to generate the leading comma, but it probably is not likely you will use this where you didn't have at least one variable in the list before the list of date named variables. Although if the macro really generted ,"&date"n then it is not working as it should have generated an actual date value and not the string '"&date"n'.

I find that it helps to avoid that type of error if you get in the habit of formatting long statements by placing the commas at the START of the lines instead of at the END of the line. They are easier to see there since the left edge is normally aligned.

[sameer112217]

Thank you very much everyone for your valuable contributions. Such high class knowledge sharing I am in love with this forum.

Thanks Tom for providing the closest answer

[Saakshi]

Hi Tom,

Can you please explain how does the below statement work ?

%let firstdt=%sysfunc(intnx(year,"&currentdt"d,0-(1=%sysfunc(month("&currentdt"d)))),date9);

[Tom]

---

@Saakshi wrote:

Hi Tom,

Can you please explain how does the below statement work ?

%let firstdt=%sysfunc(intnx(year,"&currentdt"d,0-(1=%sysfunc(month("&currentdt"d)))),date9);

---

CURRENTDT has a string in the date9 format.  Like 02JUL2017.

So "&currentdt"d is a date literal.

MONTH()  will return the month number of a date. So for July the result will be 7.

(1=7) will return a boolean true/false value. So when false it returns 0 and when true it returns 1.

0-(xxx) will return either 0 or -1 depending on if the condition is true or not. So in January it returns -1..

INTNX() will calculate a relative date. YEAR means that the interval is in years. 0 or -1 means either this year or the year before.

%sysfunc(intnx(...),date9) means that the value calculated by the INTNX() function will be formated using the date9 format.

So FIRSTDT is the first day of either this year or last year in DATE9 format.