Macro dates

[jorquec]

Hi all, 

I am not an expertise on macros so .

Could someone help me to convert this hard dates on an automatic macro dates ? So I don not need to change every month those rows manually.

Many thanks in advance.

%let today=%sysfunc(today());
%let currdt=%sysfunc(datetime());

/*** CHANGE DATES TO THE MONTH IN QUESTION ***/
%let med = '2020-01-31';    /* month last date */
%let msd = '2020-01-01';    /* month first date */
%let mid = (((Extract (YEAR From '2020-01-31') - 1990 )* 12 ) + (Extract (MONTH From '2020-01-31')));   /* month last date */
%let mth = 202001;          /* year and month */
%let med2 = 1200131;         /* month last date Teradata format */
%let msd2 = 1200101;		/* month first date Teradata format */
%let prevmed = DATE'2020-01-01'-1;    /* month first date */
%let prevmid = (((Extract (YEAR From Add_Months('2020-01-01',-1)-1) - 1990 )* 12 ) + (Extract (MONTH From Add_Months('2020-01-01',-1)+0))); /* month first date */
options mprint mlogic symbolgen;

[PaigeMiller]

Learn to use SAS date functions (and formats) rather than trying to figure out your own calendar functions using integers and text strings. Furthermore, all of your attempts to make macro variables readable by humans is completely unnecessary (unless they are to appear in titles or labels, or because certain databases require certain formats), and so today (February 11, 2020) is 21956. This works perfectly well for macros, and avoids all of the effort (and potential mistakes) you are putting into obtaining human readable macro variables.

Specifically, 

%let med = '2020-01-31';    /* month last date *

becomes

%let med = %sysevalf('31JAN20'd);

More generally, you could use

%let today = %sysfunc(today());

which determines the exact day you are running the code and stores the result in a macro variable named &TODAY.

If you want the end of the month, or middle of the month, or beginning of the month

%let beginning = %sysfunc(intnx(month,&today,0,b));
%let middle = %sysfunc(intnx(month,&today,0,m));
%let end = %sysfunc(intnx(month,&today,0,e));

and in this way, you don't have to write your own code to determine what the middle of the month is, and you don't have to write your own code to determine what the end of the month is, because this is difficult to code properly and because SAS has already done this for you.

If you want to see these values in human readable form (just to make sure you did it properly)

%put &=middle %sysfunc(putn(&middle,yymmdd.));

[Tom]

---

@jorquec wrote:

Hi all, 

I am not an expertise on macros so .

Could someone help me to convert this hard dates on an automatic macro dates ? So I don not need to change every month those rows manually.

Many thanks in advance.

%let today=%sysfunc(today());
%let currdt=%sysfunc(datetime());

/*** CHANGE DATES TO THE MONTH IN QUESTION ***/
%let med = '2020-01-31';    /* month last date */
%let msd = '2020-01-01';    /* month first date */
%let mid = (((Extract (YEAR From '2020-01-31') - 1990 )* 12 ) + (Extract (MONTH From '2020-01-31')));   /* month last date */
%let mth = 202001;          /* year and month */
%let med2 = 1200131;         /* month last date Teradata format */
%let msd2 = 1200101;		/* month first date Teradata format */
%let prevmed = DATE'2020-01-01'-1;    /* month first date */
%let prevmid = (((Extract (YEAR From Add_Months('2020-01-01',-1)-1) - 1990 )* 12 ) + (Extract (MONTH From Add_Months('2020-01-01',-1)+0))); /* month first date */
options mprint mlogic symbolgen;

---

Only the first value will be a valid SAS date value (number of days since 1960). The second one is a SAS datetime values (number of seconds since 1960).  If you really have a datetime value then to convert it to date value use the DATEPART() function.

It looks like you are trying to generate some code to pass into some external SQL database.  The style you have in PREVMED, DATE 'YYYY-MM-DD' ,  is probably the most reliable style to use for this as it explicitly tells the code parser you meant a date instead of string (like the style in MED) or a number (like the style in MED2).  But why did you include the minus 1 in PREVMED?  What does that mean?

The tricky part of generating that style using macro code is generating the single quotes. Most database systems will not accept double quotes instead.  In general it might be easier to just do it in SAS code instead.

To move to the start/end of a month use the INTNX() function.

data _null_;
  now=today();
  fom=intnx('month',now,0,'b');
  lom=intnx('month',now,0,'e');
  call symputx('prevmed','DATE'||quote(put(fom,yymmdd10.),"'"));
run;

I have no idea what the values you have for MED2 and MSD2 represent.  Looks like the last four digits as the month and day. But what is the 120 in the front?  

[jorquec]

HI

1200131 means 2020/01/31

1200101  means 2020/01/01 

Yes I use these dates to run proc sql on SAS enterpise guide , I use these formats as tables on Teradata that I am checking has these formats. 

[Stephanvd · Accepted solution]

Basic Concepts: 

%let med =%sysfunc(intnx(month,(&today),0,e),DDMMYYD10.); /* month last date */ %let msd = %sysfunc(intnx(month,(&today),0,b),DDMMYYD10.); /* month first date */ %let mth = %sysfunc(putn(&today, YYMMN.)); /* year and month */

/* Generating: '200131':*/
%let med2 =%sysfunc(intnx(month,(&today),0,e),YYMMDD7.); /* month last date Teradata format */

%let today=%sysfunc(today());
%let currdt=%sysfunc(datetime());

%let med =%sysfunc(intnx(month,(&today),0,e),DDMMYYD10.);    /* month last date */
%let msd = %sysfunc(intnx(month,(&today),0,b),DDMMYYD10.);    /* month first date */
%let mid = (((Extract (YEAR From &med) - 1990 )* 12 ) + (Extract (MONTH From &med)));   /* month last date */
%let mth = %sysfunc(putn(&today, YYMMN.));          /* year and month */
%let med2 =%sysfunc(intnx(month,(&today),0,e),YYMMDD7.); /* month last date Teradata format */
%let msd2 =%sysfunc(intnx(month,(&today),0,b),YYMMDD7.); /* month last date Teradata format */
%let prevmed = DATE&msd-1;    /* month first date */
%let prevmid = (((Extract (YEAR From Add_Months(&msd,-1)-1) - 1990 )* 12 ) + (Extract (MONTH From Add_Months(&msd,-1)+0))); /* month first date */
options mprint mlogic symbolgen;