Incorrect date format input to intnx function

[Uknown_user]

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;

[RW9]

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.

[Astounding · Accepted solution]

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.

[Uknown_user]

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.

[Kurt_Bremser]

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.

[Kurt_Bremser]

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