topic Re: SAS EG Prepare Error Help in SAS Programming

SAS EG Prepare Error Help

RedUser77 — Wed, 23 Nov 2022 13:43:11 GMT

Hello, below is a sample query that runs in DB2. However, all of a sudden, I am now getting a prepare error and cannot figure out why. Any ideas? The pasted code can be found below:

/*%put &FAXES;*/
%let FAXES=&FAXES, 1, 2;
/*%put &FAXES;*/

PROC SQL;
(SELECT a.CAR_ID, c.CAR_NAME AS NAME, e.MAKE||' '||e.MODEL AS MAKE_MODEL
FROM OWNER.FACTORY b
LEFT JOIN OWNER.CARS a ON b.KEY=a.KEY
LEFT JOIN OWNER.STATUSES c ON b.TIME_ID =c.TIME_ID
LEFT JOIN OWNER.DRIVERS e ON b.NAME=e.NAME
INNER JOIN (SELECT ENTITY, max(TIME) AS TIMESTAMP
FROM OWNER.FACTORY
WHERE FAX_ID='Fax'
AND ENTITY IN(&FAXES)
AND TIMESTAMP>DATE-60
GROUP BY ENTITY) d ON b.KEY=d.ENTITY AND b.TIMESTAMP=d.TIMESTAMP

WHERE b.FAX_ID='Fax' AND b.KEY IN(&FAXES) AND b.TIMESTAMP>DATE-60);

Quit;

Re: SAS EG Prepare Error Help

PeterClemmensen — Wed, 23 Nov 2022 13:44:40 GMT

Post your log please.

Re: SAS EG Prepare Error Help

RedUser77 — Wed, 23 Nov 2022 13:48:25 GMT

Not able to reveal whole log as it is sensitive.

Re: SAS EG Prepare Error Help

ChrisHemedinger — Wed, 23 Nov 2022 13:52:28 GMT

Difficult to diagnose without a log, but if it was working and now isn't, I'd check to make sure &FAXES contains what you expect.

Re: SAS EG Prepare Error Help

PeterClemmensen — Wed, 23 Nov 2022 13:54:57 GMT

What does the macro variable &faxes resolve to?

Re: SAS EG Prepare Error Help

RedUser77 — Wed, 23 Nov 2022 14:00:13 GMT

It is supposed to append dummy keys to the list to prevent errors on 0 or 1 car cases in the report. There is an error with this too all of a sudden where its log reads 'the text expression &FAXES, 1, 2 contains a recursive reference to the macro variable FAXES. The macro variable will be assigned the null value.'
Again, never seen this before as the program has always ran error-free until today.

Re: SAS EG Prepare Error Help

PeterClemmensen — Wed, 23 Nov 2022 14:02:59 GMT

Are you sure that the %let Statement has always referenced &FAXES? What is the point of that?

Re: SAS EG Prepare Error Help

RedUser77 — Wed, 23 Nov 2022 14:05:08 GMT

Yes, very positive. Been running this since June without any changes ever being made. The point of the variable is to append dummy keys to the list to prevent errors on 0 or 1 car cases in this recurring report.

Re: SAS EG Prepare Error Help

ChrisHemedinger — Wed, 23 Nov 2022 14:06:05 GMT

If FAXES was supposed to have been defined in a previous step, but sometimes isn't, then you need some error checking for defensive programming.

%if %symexist(faxes) %then %do; %let faxes = &faxes, 1, 2; %end; %else %do; %let faxes = 1, 2; %end; %put &=faxes;

You might have a bigger problem going on here with previous steps failing.