topic Re: intnx as macro variable issue in SAS Enterprise Guide

intnx as macro variable issue

DRivas10 — Tue, 18 Jun 2019 18:10:52 GMT

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.

Re: intnx as macro variable issue

Kurt_Bremser — Tue, 18 Jun 2019 18:27:45 GMT

What is stored in &sample_size?

Re: intnx as macro variable issue

tsap — Tue, 18 Jun 2019 18:35:34 GMT

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

Re: intnx as macro variable issue

DRivas10 — Tue, 18 Jun 2019 18:37:15 GMT

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.

Re: intnx as macro variable issue

Kurt_Bremser — Tue, 18 Jun 2019 18:45:32 GMT

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

Re: intnx as macro variable issue

Astounding — Tue, 18 Jun 2019 19:34:31 GMT

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

Re: intnx as macro variable issue

TomKari — Tue, 18 Jun 2019 20:17:24 GMT

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

Re: intnx as macro variable issue

Tom — Tue, 18 Jun 2019 20:34:12 GMT

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

Re: intnx as macro variable issue

Patrick — Wed, 19 Jun 2019 00:48:22 GMT

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