topic Re: date macro variables in SAS Programming

date macro variables

sameer112217 — Sat, 01 Jul 2017 08:24:48 GMT

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

Re: date macro variables

sameer112217 — Sat, 01 Jul 2017 08:28:41 GMT

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

Re: date macro variables

ChrisNZ — Sat, 01 Jul 2017 08:53:33 GMT

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.

Re: date macro variables

sameer112217 — Sat, 01 Jul 2017 08:59:29 GMT

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

Re: date macro variables

Astounding — Sat, 01 Jul 2017 10:16:14 GMT

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.

Re: date macro variables

sameer112217 — Sat, 01 Jul 2017 11:00:39 GMT

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.

Re: date macro variables

Astounding — Sat, 01 Jul 2017 11:07:38 GMT

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.

Re: date macro variables

Reeza — Sat, 01 Jul 2017 13:50:14 GMT

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?

Re: date macro variables

sameer112217 — Sat, 01 Jul 2017 14:44:42 GMT

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

Re: date macro variables

Tom — Sat, 01 Jul 2017 16:06:54 GMT

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
;

Re: date macro variables

sameer112217 — Sat, 01 Jul 2017 19:45:57 GMT

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

Re: date macro variables

Tom — Sat, 01 Jul 2017 22:55:33 GMT

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.

Re: date macro variables

sameer112217 — Sun, 02 Jul 2017 10:51:48 GMT

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

Re: date macro variables

Saakshi — Mon, 03 Jul 2017 06:54:44 GMT

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

Re: date macro variables

Tom — Mon, 03 Jul 2017 14:15:53 GMT

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

Re: date macro variables

Saakshi — Fri, 07 Jul 2017 05:14:26 GMT

perfect . thanks for such a beautiful explaination .. i am loving it 🙂