BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Uknown_user
Quartz | Level 8

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;

 

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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

5 REPLIES 5
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
PROC Star

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
Quartz | Level 8

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
Super User

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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