topic Re: Include statement in macro not recognising file in SAS Programming

Include statement in macro not recognising file

PatrickBros1993 — Fri, 17 Aug 2018 08:50:20 GMT

%macro Update();

%global Updated MaxDate;
%include 'Y:\Marketing\mi_analysis\Reports\04.Digital\Revised Digital Reporting\01 Code\Tidied\02a - Data Load.sas';

%if &NewFiles.=0 %then %do;
	%do %until(&LastRun.=1);

	data _null_;
 	x = sleep(900);
 	run;

	data now;
	format now hhmm.;
	now=time();
	if now>='09:15't then LastRun=1; else LastRun=0;
	call symput('now',now);
	call symput('LastRun',LastRun);
	run;

	%put  &now;
	%put  &LastRun;

	%include 'Y:\Marketing\mi_analysis\Reports\04.Digital\Revised Digital Reporting\01 Code\Tidied\02a - Data Load.sas';
	
	%if &NewFiles.>0 %then %do;
	%let LastRun=1;
	%end;
	%else %do;
	%end;

	%end;
%end;
%else %do;
%end;


%if &NewFiles.=0 %then %do;
%email("my email address","Files not arrived");
%end;
%else %do;
%end;


%mend;

%Update();

I have some code here which is meant to load in a series of CSV files each morning from an SFTP folder. I've written a macro so that it runs once, and if the files haven't yet arrived then it will wait 15 minutes and try again, and repeats this as such until a specified time (9:15) when it stops and sends out an email notifying the files haven't arrived. This seems to work fine when I run it in from a session, but run from the scheduler it keeps failing on the 'do until' loop - I get the following message:

WARNING: Physical file does not exist, Y:\Marketing\mi_analysis\Reports\04.Digital\Revised Digital
Reporting\01 Code\Tidied\02a - Data Load.sas.

This is quite frustrating as it recognises the file and runs on the initial %include statement, and the second include statement is a copy and paste of the first so it's definitely not a typo or anything. I'm guessing it might be something to do with the fact it's in that conditional part of the macro which is causing a problem. Does anyone have any ideas?? As mentioned before it's only when run from the scheduler that this occurs (I don't know much about the technical details of this scheduler but if it's relevant I can ask).

Thanks!

Re: Include statement in macro not recognising file

RW9 — Fri, 17 Aug 2018 08:58:07 GMT

Probably the scheduler does not have access to that network or path. The error is pretty clear in that it can't find the file, so your scheduler person is the one to ask.

As a note, if you have a scheduler program/person, why not ask them if there is an On File Received trigger, rather than waiting around re-scanning every few minutes your end, have a trigger that calls your import program only when a new file is received. That would be my preferred solution.

Re: Include statement in macro not recognising file

PatrickBros1993 — Fri, 17 Aug 2018 09:07:56 GMT

Thanks, I'm also waiting for a reply from the SAS administrators.

The problem is that all the historic files are sat on that SFTP, so rather than looking for the presence of a file(s) it needs to look specifically for the presence of the latest files containing data for the days since the last time the code was run. That means it has to run the import program which selects only the latest files.

Re: Include statement in macro not recognising file

Kurt_Bremser — Fri, 17 Aug 2018 09:13:03 GMT

How can you be sure that the first %include works, but not the second?

Re: Include statement in macro not recognising file

PatrickBros1993 — Fri, 17 Aug 2018 09:15:06 GMT

Because the logs show it runs the first statement fine, then when it goes into the do until loop I get the aforementioned error every time it tries the include statement.

Re: Include statement in macro not recognising file

RW9 — Fri, 17 Aug 2018 09:22:23 GMT

But the file is a new file which appears on there? Or do you mean all the data is there now and you are only importing a bit of it at a time? If its a new file, then you just need an On New File trigger. If all the data is there, then why not just import it all now to datasets and be done with it?

Re: Include statement in macro not recognising file

Kurt_Bremser — Fri, 17 Aug 2018 09:58:23 GMT

Then there are two possibilities as far as I can see:

something causes the path to the file to go "missing" (change in permission, unmount the share, ....)
you are mis-reading the log. Macro timing can be very tricky.

Add %put statements frequently, so you can follow all the possible execution paths. Set options source2, so you can see all the code from the includes in the log.

Re: Include statement in macro not recognising file

Patrick — Fri, 17 Aug 2018 10:35:19 GMT

@PatrickBros1993

So if this runs fine out of your session but not when scheduled then I'd look out for two things first:

1. Permissions

Your session will run under your user while the scheduled job will run under a batch user. If this user doesn't have access to the file (doesn't "see" it) then you could get such an error.

2. Timing

You say the 2nd include is a copy of the file. When does this copy happen? I don't believe this applies to your problem but I've seen cases where a scheduled batch process got trigger "on file arrival" but then the process failed because the SFTP process creating the file hadn't finished yet - so the file "arrived" but wasn't fully available yet when the process consuming the data got triggered.

Re: Include statement in macro not recognising file

PatrickBros1993 — Fri, 17 Aug 2018 11:01:10 GMT

If it's able to look for new files I imagine the On New File trigger could work for what I'm doing. If I can't make the code above work I'll look into that, thanks for the tip! To clarify, it's an ongoing list of raw files sat in the SFTP, they get added to every day with data from the previous day. The code will take only the latest data, manipulate it as desired and then append it into another dataset. Processing everything at once would take ages so isn't suitable for a daily job.