creating end date variable inside sas macro using intnx

[vsharipriya]

I extracted month and year from a string and would like to create a end date of the month, store it in a macro variable. I tried this example. This doesnt work in the following program.

Options mprint symbolgen;
%macro mac_name(name);

  %let get_month = %substr(&get_date,5,2); /* get_Date is in the form of yyyymm , ex: 201512) */
  %let get_year = %substr(&get_Date,1,4);
 %let date=  %input(catt(&get_year, &get_month), yymmn6.) ;
/* I want to get the end date of the month */
 %let end_date=%sysfunc(intnx('month',&date, 0, 'end'),yymmdd10.);

%do; 
 		data x;
                set y;
where mem_date= &end_date;
run;
%end;

%mend mac_name;

[Astounding]

While this can be debugged, you are making life very difficult by trying to do the processing in macro language.  Since you already have &GET_DATE, why not use it?

data x;

set y;

where mem_date + 1 = intnx('month', input("&get_date.01", yymmdd8.), +1);

run;

[vsharipriya]

The reason I am creating a macro variable is I am calling this macro by passing several datasets like this:

data _null_;
 set sashelp.vmember( where=(libname='X' and memtype='DATA'));
 call execute('%mac_name('||strip(memname)||')');
run;

mac_name has the form  XYZ_PQR_2015_20151, XYZ_PQR_2015_20152, XYZ_PQR_2015_20153..XYZ_PQR_2015_201512

I created a macro variable get_Date by extracting the last part of the name , to identify each dataset by its month. 

 %let get_date = %scan(&tname,4,'_');

Hence get_date varies with name, so should the end_Date.

[Astounding]

Under those conditions, that's even more reason to do your processing in a DATA step.  You are already working in a DATA step, and you already have a DATA step variable MEMNAME that contains the key information.  Let the same DATA step do the processing.

It might be easiest to redefine %MAC_NAME so that it uses a second parameter.  Then the same DATA step can calculate the proper cutoff date, and add to the CALL EXECUTE statement to pass that second parameter.

[vsharipriya]

Thanks @Astounding.
You meant, process the macro statements for calculating cut off date in the data step before call execute? Then again, my problem is I am not able to generate the date in the required format.

Here, if I pass XYZ_PQR_2015_201512 as name , the end_Date should be 2015-12-31 , if I pass XYZ_PQR_2015_201512 as name, the end _Date should be 2015-01-31 .

Later in the where statement I want to subset records with mem_date equal to this end_Date.

I thought it is easier to do this in a separate macro as each time the macro is executed by call execute, the end_Date changes with respect to the name passed.

[Astounding]

Yes, that's the idea.  Right now, each CALL EXECUTE generates a statement along these lines:

%mac_name(XYZ_PQR_2015_201512)

The idea is to change the definition of %MAC_NAME so that this version would be effective:

%mac_name(XYZ_PQR_2015_201512,#####)

In this example, ##### represents a SAS date.  So you would need to redefine the macro in a way that it accepts two parameters (the name of the incoming file, and the cutoff date).  The easiest way to both see that the macro is doing the correct thing and to pass the correct date would be to use DATE9 format:

%mac_name(XYZ_PQR_2015_20152,31Dec2015)

You can make that value available as a DATA step variable.  Prior to CALL EXECUTE, you already have a value for MEMNAME.  You could code, for example:

YM = scan(memname, -1, '_');

That gets you the five or six digits at the end, as a character string.  Then replace it.  There are many ways, but here's one:

year = input(ym, 4.);

mon = input( substr(ym, 5), 2.);

beginning_of_month = mdy(mon, 1, year);

ym = put( intnx('month', beginning_of_month, 0, 'E'), date9.);

I can't test it right now ... it looks about right although I wouldn't be surprised if somebody came up with a shortcut.

Right now, you are using:

call execute( '%mac_name(' || strip(memname) || ')' );

That would become:

call execute( '%mac_name(' || strip(memname) || ', ' || strip(ym) || ')' );

So you would be calling the macro with two parameters instead of one.  You would need to change the macro definition, but all the information is available, ready to pass to the macro because the DATA step can easily calculate the proper values.  Inside the macro, you could compare:

where mem_date = "&second_parameter"d;

Use whatever the name of the second parameter is, when you rewrite the macro definition.

OK, I've rambled long enough ... that's just my approach and you are certainly a lot closer than you were originally with the suggestions you have tried from ballardw.

[vsharipriya]

Thanks @Astounding , for the details.

This approach works too. But when I am trying to subset in the where clause,

where mem_date = "&second_parameter"d;

It throws an error:

ERROR:Invalid date/time/datetime constant '2015-01-31'd

ERROR: Syntax error while parsing WHERE clause

I am not sure why it cannot resolve 'end_Date'd in the where clause.

@ballardw , any thoughts on this?

[Astounding]

Date constants in SAS must use DATE9 format.  This is illegal:

'2015-01-31'd

This is legal:

'31Jan2015'd

[vsharipriya]

Thanks @ballardw . So, when I pass my end_date as a parameter to my macro , following an approach suggested by @Astounding ,

in this statement -

format end_Date date9.;

call execute('%mac_name('||strip(memname)||','||end_date||')');

I formatted my end_Date as date9. before passing this parameter.

So in my macro, the where clause still gives the error::Invalid date/time/datetime constant '&end_date'd .

In the log, I can see that the end_Date actually resolves to '31JAN2015' , which I believe it is considering as a string.

Do I need to format the end_Date inside the macro, before the where clause too? If so, how can I format the date inside a data step in my macro?

Thanks.

[Astounding]

On the CALL EXECUTE statement, you refer to END_DATE.  It is still numeric, although it has a format connected with it.  (In fact, if END_DATE were character, you would get an error message for trying to assign a numeric format to a character variable.)  To get the CALL EXECUTE statement to include the END_DATE in the DATE9 format, you have to replace ||END_DATE|| with:

||put(end_date, date9.)||

[vsharipriya]

Thanks @Astounding

I tried but still I get the following error:

ERROR: Invalid date/time/datetime constant '&end_date'd.

ERROR: Syntax error while parsing WHERE clause.

Here is my code:

data _null_;
 set sashelp.vmember( where=(libname='X'and memtype='DATA'));
 get_date = scan(memname,4,'_'); 
 get_year = substr(get_date,1,4);
 date= input(get_date,yymmn6.); 
 if get_year="2015" then end_date=(intnx('month',date, 0, 'end'));
 if get_year="2016" then end_date= '31Dec2015'd;
 call execute('%mac_name('||strip(memname)||','||put(end_date, date9.)||')');
 ;
run;

options mprint symbolgen;
%macro mac_name(mac_name,end_date);
%do;
    		data temp;
		set temp;
		where mem_date='&end_date'd;
		run;
       proc append base=date_test data=temp force;
       run;
 %end;
 %mend mac_name;

[Astounding]

Within your macro definition, you have to use double quotes around references to macro variables.  Single quotes prevent the resolution of macro variables.  Switch to:

where mem_date = "&end_date"d;

[vsharipriya]

I tried this and it worked:

%put %sysfunc(inputn(&end_date,date9.)); /* before the data step in macro*/

where mem_Date= "&end_date."d;

[ballardw]

Please describe how "it doesn't work". If you get error messages include the log with the code submitted and the entire error message. If the error occurs with a macro involved use Options Mprint Symbolgen to get details of how the macro actually executed.

If you get unexpected results then show the actual result and the desired result.

If you get no result show the expected result.

%INPUT is NOT the same as the Input datastep instruction. When you use %INPUT the macro processor is expecting you to use values entered into a %Window dialogue box.

I must ask why did you disassemble a 6 character string into a 4 and 2 character string just to put them back together again?

Please examine this code:

%let get_date= 201512;
%let date = %sysfunc(inputn(&get_date,yymmn6.)); 
%put &date;

The is no need for, and would be an error, to include the format in this statement:

%let end_date=%sysfunc(intnx('month',&date, 0, 'end'),yymmdd10.);

Try

%let end_date=%sysfunc(intnx('month',&date, 0, 'end'));

[vsharipriya]

Thanks Ballard.

Here is the error log for the above code that I tried.

4168 %let get_date= 201512;
4169 %let date = %sysfunc(inputn(&get_date,yymmn6.));
SYMBOLGEN: Macro variable GET_DATE resolves to 201512
4170 %put &date;
SYMBOLGEN: Macro variable DATE resolves to 20423
20423
4171 %let end_date=%sysfunc(intnx('month',&date, 0, 'end'));
SYMBOLGEN: Macro variable DATE resolves to 20423
WARNING: An argument to the function INTNX referenced by the %SYSFUNC or %QSYSFUNC macro function
is out of range.
NOTE: Mathematical operations could not be performed during %SYSFUNC function execution. The
result of the operations have been set to a missing value.