Incorrect date format input to intnx function

Accepted Solution Solved
Reply
Contributor
Posts: 44
Accepted Solution

Incorrect date format input to intnx function

[ Edited ]

Hello all,

 

could any one give a hand with the following piece of code:

 

see attachment

 

It returns this error:

 

WARNING: Apparent symbolic reference CYCLE_END_DT not resolved.

ERROR: Invalid date/time/datetime constant "&cycle_end_dt"d.

WARNING: Apparent symbolic reference CYCLE_END_DT not resolved.

ERROR: Invalid date/time/datetime constant "&cycle_end_dt"dt.

ERROR: Syntax error while parsing WHERE clause.

 

Thanks!

 

%Macro m(country,month);

data _null_;
month_end_dt = put(intnx('month',mdy(substr("&cycle",1,length("&cycle")-2),1,substr("&cycle",length("&cycle")-1,2)),-1,'end'),date9.)||':23:59:59.000';
year = year(input(month_end_dt,date9.));
month = substr(compress(0||month(input(month_end_dt,date9.))),length(compress(0||month(input(month_end_dt,date9.))))-1,2);
year_month = cat(year,"_",month);
call symput('month_end_dt',put(input(month_end_dt,datetime22.3),datetime22.3));
call symput('ym',year_month);
run;

DATA	basedata_1_&country._&ym;
SET		DM_CMDM.VW_TRX_LINE (keep= PK_COUNTRY_ID FK_CONSUMER_ID TRX_EVENT_END PK_RETAIL_TRANS_LINE_ITEM_ID FK_PRODUCT_ID SALES_QUANTITY ACTUAL_SALES_PRICE POINTS);
WHERE pk_country_id = "&country"
AND INTNX('DTWEEK',"&cycle_end_dt"d,-13,'S') < TRX_EVENT_END <= "&cycle_end_dt"dt;
RUN;

%mend m;

data _null_;
   array x[1] $2 ('BG'/* 'CZ' 'DE' 'HU' 'PL' 'RU' 'SG' 'SK' 'TH'*/);
   	do j=1 to dim(x);
		do i = 8 to 12;
			country = x[j];
			cycle = compress(i||'16');
			call symput('country',country);
			call symput('cycle',cycle);
			call execute ('%m(&country,&cycle)');
		end;
  	end;
run;

 


Accepted Solutions
Solution
‎02-14-2017 10:24 AM
Super User
Posts: 5,082

Re: Incorrect date format input to intnx function

You have a couple of issues with this code.  First, where is &CYCLE_END_DT supposed to come from?  This code tries to use it, but never creates it.  So a portion of the code is missing.

 

Second, what is in it?  One of these must be incorrect (or both, I guess):

 

"&cycle_end_dt"d

"&cycle_end_dt"dt

 

The same line of code refers to both.  The top reference assumes that the contents of &CYCLE_END_DT represent a date.  The bottom reference assumes that the contents of &CYCLE_END_DT represent a date-time.  It's not possible for both to be correct.

View solution in original post


All Replies
Super User
Super User
Posts: 7,401

Re: Incorrect date format input to intnx function

That log snippet is telling you that either that macro variable was never created, or it is not in the scope of the current activities.  As for attachments, post code in a code window ({i} above the post area) with accompanying test data - in the form of a datastep.  This is so we can help you with further questions.

Solution
‎02-14-2017 10:24 AM
Super User
Posts: 5,082

Re: Incorrect date format input to intnx function

You have a couple of issues with this code.  First, where is &CYCLE_END_DT supposed to come from?  This code tries to use it, but never creates it.  So a portion of the code is missing.

 

Second, what is in it?  One of these must be incorrect (or both, I guess):

 

"&cycle_end_dt"d

"&cycle_end_dt"dt

 

The same line of code refers to both.  The top reference assumes that the contents of &CYCLE_END_DT represent a date.  The bottom reference assumes that the contents of &CYCLE_END_DT represent a date-time.  It's not possible for both to be correct.

Contributor
Posts: 44

Re: Incorrect date format input to intnx function

Thx for your suggestion. I mistakenly pasted a macro variable cycle into the place where derived local variable should be called. It is working now.

Super User
Posts: 6,936

Re: Incorrect date format input to intnx function

First of all, I'd do the call execute differently:

data _null_;
array x[1] $2 ('BG'/* 'CZ' 'DE' 'HU' 'PL' 'RU' 'SG' 'SK' 'TH'*/);
do j=1 to dim(x);
  do i = 8 to 12;
    country = x[j];
    cycle = compress(i||'16');
    call execute ('%m('!!trim(country)!!','!!trim(cycle)!!')');
  end;
end;
run;

Keep in mind that code that is submitted with call execute will run after the current data step has finished, and the macro variables will have the last values set, so you get a number of runs with identical parameters.

 

 

And I see absolutely no place in your macro where cycle_end_dt is set, so it's no miracle it can't be resolved.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 6,936

Re: Incorrect date format input to intnx function

Copied the code in the original post from the attachment to a code window. It seems I have retained some of my superpowers Smiley Happy

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 160 views
  • 0 likes
  • 4 in conversation