BookmarkSubscribeRSS Feed
PatrickBros1993
Calcite | Level 5
%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!

8 REPLIES 8
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

PatrickBros1993
Calcite | Level 5

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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?

PatrickBros1993
Calcite | Level 5

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.

PatrickBros1993
Calcite | Level 5
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.
Kurt_Bremser
Super User

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.

Patrick
Opal | Level 21

@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.

 

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
  • 8 replies
  • 692 views
  • 0 likes
  • 4 in conversation