intnx as macro variable issue

[DRivas10]

Hello everyone,

I'm new to this community and I would like to ask for your help because I've been trying for 2h and I can't manage to solve my problem.

My goal  is that from a date that you introduce in a prompt (201803), you get the same date but 11 months before (201704) in this case.

I'd like to do the task as a macro variable because with a data procedure I don't know how to create a variable to use in open code out of it.

The code I'm currently using is the following:

%let end=201803;

%let aux=%sysfunc(inputn(&end,yymmn6.),ddmmyy8.);

%let start_aux=%sysfunc(intnx(month,&aux,-&sample_size.+1,same),ddmmyy8.);

%let start_aux1=%sysfunc(inputn(&start_aux.,ddmmyy8.),yymmn6.);

When I go to the journal after doing some puts I get the following:

61         %put &aux.;

01/03/18

62         %put &start_aux.;

01/02/59

63         %put &start_aux1.;

195902

Could please anyone help me?

Thank you in advance.

[Kurt_Bremser]

What is stored in &sample_size?

[tsap]

%let end=201803;
%let aux=%sysfunc(inputn(&end,yymmn6.),ddmmyy8.);

%let start_aux=%sysfunc(intnx(month,(%sysfunc(inputn(&end,yymmn6.))), -11,same),ddmmyy8.);
%let start_aux1=%sysfunc(inputn(&start_aux.,ddmmyy8.),yymmn6.);

%PUT &end.;
%put &aux.;				/*	01/03/18	*/
%put &start_aux.;		/* 	01/02/59	*/
%put &start_aux1.;		/*	195902		*/

	

I replaced the 'sample size macro logic' with 11 for the purpose of the task requested in your post. You can just plug that logic back in and it should function the same way it is performing now with the '-11' in its place.

Hope this helps.

[DRivas10]

Hello Kurt,
It would be a variable from the prompt (12 in this case). I also tried putting a number (11) and the same issue is happening.
Let me know if you need any further clarification and thank you for your time.

[Kurt_Bremser]

Keep in mind that

• the macro preprocessor is a text processing engine and does calculations (on its own) only in some special circumstances
• date functions expect raw date values as input

Try this

%let aux=%sysfunc(inputn(&end,yymmn6.));
%let start_aux=%sysfunc(intnx(month,&aux,%eval(-&sample_size.+1),same),ddmmyy8.);

[Astounding]

&aux has the wrong value. It should be unformatted (not using ddmmyy8.) to feed the proper value into INTNX.

In macro language, 01/03/18 becomes 0, the result of %eval(01/03/18)

[TomKari]

Personally, my head always starts to hurt when I get into too many macro functions and variables. It's less sophisticated, but I find a data step clearer.

Tom

%let aux = 201803;

data _null_;
	OriginalDate = mdy(input(substr("&aux.", 5, 2), best2.), 1, input(substr("&aux.", 1, 4), best4.));
	ModifiedDate = intnx("month", OriginalDate, -11, "same");
	call symput("aux1", put(ModifiedDate, yymmn6.));
run;

%put &=aux.;
%put &=aux1.;

[Tom · Accepted solution]

END does not have a date value, but you are using INPUTN() to convert it into one in some places.

AUX also does not have a date value, but you are trying to use it as if it did.

Why did you use the DDMMYY8 format to set the value of AUX?  Do you need that string somewhere else?  If so why would you use a date format that does not include the century?

So you could either copy the whole %SYSFUNC(INPUTN()) code to convert AUX into a date value where you need a date value.

Or store the date value into another macro variable and use that one in your calculations and leave AUX and START_AUX for whatever display purpose you need that other string for.

Looks like you are trying to do this:

%let end=201803;
%let sample_size=2;
%let start=%sysfunc(intnx(month,%sysfunc(inputn(&end,yymmn6)),-&sample_size+1,s),yymmn6);

21    %put &=end &=start;
END=201803 START=201802

[Patrick]

@DRivas10 

Even when starting with a macro variable and wanting a macro variable as result I find it often easier to use a data step in between if there is more than one function involved.

Below sample code for both a data step approach and a macro only approach.

%let end=201803;

data _null_;
  have=input("&end",yymmn6.);
  want=intnx('month',have,-11,'b');
  call symputx('start_aux1',put(want,yymmn6.));
  stop;
run;
%put &=start_aux1;

%let start_aux2=%sysfunc(intnx(month,%sysfunc(inputn(&end,yymmn6.)),-11,b),yymmn6.);
%put &=start_aux2;